MySQL操作

时间：2018-06-15 22:17:14 收藏：0 阅读：195

1.SQL　　　　　　　　

1.SQL

DDL：create、alter、drop
DCL：grant、revoke
DML：update、insert、delete
DQL：select

1.1.DDL

创建数据库

语法：create database dbName [character set charsetName [collate colName]]

mysql> create database db_users character set binary;
mysql> create database db_college;

View Code

修改数据库

mysql> alter database db_users character set utf8;

View Code

删除数据库

mysql> drop database db_users;

View Code

数据类型对照
Java	MySQL
byte/short/int/long	tinyint/smallint/int/bigint
float/double	float/double
boolean	bit
char/String	char/varchar
Date	date/time/datetime/timestamp
File	blog/text

创建表

mysql> create table t_student(
    ->  id int primary key auto_increment,
    ->  name nchar(30) not null,
    ->  gender char(1),
    ->  score double,
    ->  state char(1) default 1,
    ->  idcard int(18) unique
    ->  );

View Code

修改表结构

增、删、改表结构，分别用到关键字：add、drop、change、modify

增加列、修改列定义、修改列名称、删除列

mysql> alter table t_student add descStu varchar(150);
mysql> alter table t_student modify descStu varchar(350);
mysql> alter table t_student change descStu descption varchar(350);
mysql> alter table t_student drop descption;

View Code

表的重命名、修改字符集

mysql> rename table t_student to t_stuItheima;
mysql> alter table t_stuitheima character set utf8;

View Code

1.2.DML

修改元组属性、删除元组

mysql> update user set password=password("admin") where host="::1" and user="root";
mysql> delete from user where user is null;

View Code

插入数据

mysql> insert into t_student values(null, "maqi",null,234.234,null,2342342);
mysql> insert into t_student (id,name,gender,score,state,idcard) values(null, "maqi",null,234.234,null,2342342);

View Code

1.3.备库

备库时，直接指定库名。还原数据时，如果把库都删除了，须另外创建。

C:\Users\Administrator\Desktop>mysqldump -uroot test > test.sql

View Code

mysql> source test.sql;
C:\Users\Administrator\Desktop>mysqldump -uroot test < test.sql

View Code

中括号，表示输入、输出重定向。

原文：https://www.cnblogs.com/argor/p/9019650.html