mysql DDL DML

简介

DDL：DDL语句是定义语言的缩写，是对数据库内部的对象进行创建、删除、的操语句，

DML：DML只对表的数据进行操作，而不涉及表的定义、结构的修改，更不会涉及其它对象。

完成：添加（creat），删除（drop），授权（grant），改密码（set），增删除字段（alter），改名（rename，change），修改数据（update），插入数据（insert）

DDL

----Data Definition Language 数据库定义语言

DML

----Data Manipulation Language 数据操纵语言

DDL语句是定义语言的缩写，是对数据库内部的对象进行创建、删除、的操语句，它和DML语句最大的区别是DML只对表的数据进行操作，而不涉及表的定义、结构的修改，更不会涉及其它对象。

1、添加

命令:CREATE USER  ' username'@'host ' IDENTIFIED BY 'password';

host - 指定该用户在哪个主机上可以登陆，此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录，如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录;也可以指定某台机器可以远程登录;

实践：

mysql> create user 'dog'@'localhost' identified by '775120@Lai';

Query OK, 0 rows affected (0.00 sec)

注意：

1、5.7开始，集成了密码强壮度检测插件，要求密码必须包含数字，字母，字符，过于简单的密码会导致创建用户失败

2、MySQL5.7 mysql.user表没有password字段改 authentication_string ；

2、授权

命令:GRANT privileges ON databasename.tablename TO  'username'@'host'

privileges-用户操作权限；

如INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

databasename-数据库名

tablename-表名

如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.*，表示授权所有的库跟表

实践：

mysql> grant select, insert on *.* to dog@localhost;

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to dog@localhost;

Query OK, 0 rows affected (0.00 sec)

3、创建用户同时授权

即将上面的合一

实践：

mysql> grant all on *.* to cat@localhost identified by '775120@Lai';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

注意，创建完直播必须执行：flush privileges

4、修改用户密码

命令:SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

实践

mysql> set password for dog@localhost=password('775120@LAi');

Query OK, 0 rows affected, 1 warning (0.00 sec)

5、查看&撤销用户权限

命令: REVOKE privilege ON databasename.tablename FROM  'username'@'host' ;

命令: DROP USER  'username'@'host' ;

实践：

mysql> revoke select on *.* from dog@localhost;

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for dog@localhost;

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for dog@localhost                                                                                                                                                                                                                                                                                                                              |

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'dog'@'localhost' |

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>

6、删除用户

命令: DROP USER  'username'@'host' ;

实践：

mysql> drop user dog@localhost;

Query OK, 0 rows affected (0.01 sec)

7、建库&表，插入数据，删除数据，

 

参考资料

建库和建表的实例1

drop database if exists school; //如果存在SCHOOL则删除

use school; //打开库SCHOOL

(

    name char(10) not null,

    year date

insert into teacher values(”,’allen’,'大连一中’,'1976-10-10′);

1、你可以将以上命令原样写入一个文本文件中，假设为school.sql，然后复制到c:\\下，并在DOS状态进入目录[url=file://\\mysql\\bin]\\mysql\\bin[/url]，然后键入以下命令：

如果成功，空出一行无任何显示；如有错误，会有提示。（以上命令已经调试，你只要将//的注释去掉即可使用）。

2、或者进入命令行后使用 mysql> source c:\\school.sql; 也可以将school.sql文件导入数据库中。

建库和建表的实例2

create database school; //建立库SCHOOL

create table teacher //建立表TEACHER

    id int(3) auto_increment not null primary key,

    address varchar(50) default ''深圳'',

); //建表结束

//以下为插入字段

insert into teacher values('''',''jack'',''深圳一中'',''1975-12-23'');

注：在建表中

create database school; //建立库SCHOOL

create table teacher //建立表TEACHER

    id int(3) auto_increment not null primary key,

    address varchar(50) default ‘深圳’,

); //建表结束

//以下为插入字段

insert into teacher values(”,’jack’,'大连二中’,'1975-12-23′);

如果你在mysql提示符键入上面的命令也可以，但不方便调试。

    mysql -uroot -p密码 < c:\\school.sql

drop database if exists school; //如果存在SCHOOL则删除

use school; //打开库SCHOOL

(

    name char(10) not null,

    year date

insert into teacher values('''',''glchengang'',''深圳一中'',''1976-10-10'');

1、将ID设为长度为3的数字字段:int(3)；并让它每个记录自动加一:auto_increment；并不能为空:not null；而且让他成为主字段primary key。

2、将NAME设为长度为10的字符字段

3、将ADDRESS设为长度50的字符字段，而且缺省值为深圳。

4、将YEAR设为日期字段。

创建库

mysql> grant all on school.* to xiaozhang@localhost identified by '775120@Xiaozhang';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show databases

    -> ;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| school             |

| sys                |

+--------------------+

5 rows in set (0.01 sec)

删除数据库

命令：drop database <name>;

删除不确定是否存在的数据库

mysql> drop database if exists techer;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop database if exists school;

Query OK, 1 row affected (0.00 sec)

创建表

mysql> create table techer(

    -> id int(3) not null primary key auto_increment,

    -> name char(20) not null,

    -> adress varchar(50) default'shenzheng',

    -> year date

    -> );

Query OK, 0 rows affected (0.01 sec)

删除表

命令：drop table <表名>

例如：删除表名为 MyClass 的表

   mysql> drop table MyClass;

DROP TABLE用于取消一个或多个表。您必须有每个表的DROP权限。所有的表数据和表定义会被取消，所以使用本语句要小心！

注意：对于一个带分区的表，DROP TABLE会永久性地取消表定义，取消各分区，并取消储存在这些分区中的所有数据。DROP TABLE还会取消与被取消的表有关联的分区定义（.par）文件。

对与不存在的表，使用IF EXISTS用于防止错误发生。当使用IF EXISTS时，对于每个不存在的表，会生成一个NOTE。

插入表值

mysql> insert into techer

    -> values

    -> (1, 'tom', 'guangzhou','1990-07-29');

Query OK, 1 row affected (0.00 sec)

mysql> select * from techer;

+----+------+-----------+------------+

| id | name | adress    | year       |

+----+------+-----------+------------+

|  1 | tom  | guangzhou | 1990-07-29 |

+----+------+-----------+------------+

1 row in set (0.00 sec)

mysql> insert into techer

    -> values

    -> ('', 'jhon', newyork,'1990-08-01');

ERROR 1054 (42S22): Unknown column 'newyork' in 'field list'

mysql> insert into techer

    -> values

    -> ('','jhon','newyork','1990-08-01');

ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1

mysql> insert into techer

    -> values

    -> ( 0,'jhon','newyork','1990-08-01');

Query OK, 1 row affected (0.00 sec)

mysql> select * from techer;

+----+------+-----------+------------+

| id | name | adress    | year       |

+----+------+-----------+------------+

|  1 | tom  | guangzhou | 1990-07-29 |

|   2 | jhon | newyork   | 1990-08-01 |

+----+------+-----------+------------+

2 rows in set (0.00 sec)

mysql> insert into techer

    -> values

    -> ( 0,'sk', 'hongkong','1990-07-28');

Query OK, 1 row affected (0.00 sec)

mysql> select * from techer;

+----+------+-----------+------------+

| id | name | adress    | year       |

+----+------+-----------+------------+

|  1 | tom  | guangzhou | 1990-07-29 |

|  2 | jhon | newyork   | 1990-08-01 |

|   3 | sk   | hongkong  | 1990-07-28 |

+----+------+-----------+------------+

3 rows in set (0.00 sec)

Ignoring query to other database

mysql -u user -p

链接数据库的时候缺少参数所致

删除表值

mysql> delete from techer where id=3;

Query OK, 1 row affected (0.00 sec)

mysql> select * from techer;

+----+------+-----------+------------+

| id | name | adress    | year       |

+----+------+-----------+------------+

|  1 | tom  | guangzhou | 1990-07-29 |

|  2 | jhon | newyork   | 1990-08-01 |

+----+------+-----------+------------+

2 rows in set (0.00 sec)

修改表值

mysql> update techer set name='marry' where id=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from techer;

+----+-------+-----------+------------+

| id | name  | adress    | year       |

+----+-------+-----------+------------+

|  1 | tom   | guangzhou | 1990-07-29 |

|  2 | marry | newyork   | 1990-08-01 |

+----+-------+-----------+------------+

2 rows in set (0.00 sec)

修改表类型

语法：

ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

如将上表的name varchar(256)改成char(128)：

Sql代码  

1. ALTERTABLE tablename MODIFYnameCHAR(128) NOTNULL;  

ALTER TABLE tablename MODIFY name CHAR(128) NOT NULL;

  mysql> alter table techer modify name char(64) not null;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

增加表字段

Sql代码  

语法：  

ALTERTABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name];  

如给上表添加age字段：  

ALTERTABLE tablename ADD age INT(11) NOTNULL;  

语法：

ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name];

如给上表添加age字段：

ALTER TABLE tablename ADD age INT(11) NOT NULL;

mysql> select * from techer;

+----+------+

| id | name |

+----+------+

|  1 | tom  |

+----+------+

1 row in set (0.00 sec)

mysql> alter table techer add year date;

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from techer;

+----+------+------+

| id | name | year |

+----+------+------+

|  1 | tom  | NULL |

+----+------+------+

1 row in set (0.00 sec)

删除表字段

Sql代码  

1. 语法：  
2. ALTERTABLE tablename DROP [COLUMN] col_name;  
3. 如删除上表age字段：  
4. ALTERTABLE tablename DROP age;  

语法：

ALTER TABLE tablename DROP [COLUMN] col_name;

如删除上表age字段：

ALTER TABLE tablename DROP age;

mysql> select * from techer;

+----+------+------------+

| id | name | year       |

+----+------+------------+

|  1 | tom  | 1990-07-29 |

+----+------+------------+

1 row in set (0.00 sec)

mysql> alter table techer modify name char(64) not null;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table techer drop year;

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from techer;

+----+------+

| id | name |

+----+------+

|  1 | tom  |

+----+------+

1 row in set (0.00 sec)

字段改名

Sql代码  

1. 语法：  
2. ALTERTABLE tablename CHANGE [COLUMN] old_col_name new_col_definition [FIRST | AFTER col_name];  
3. 如修改上表字段name名为uname：  
4. ALTERTABLE tablename CHANGE name uname CHAR(128);  

语法：

ALTER TABLE tablename CHANGE [COLUMN] old_col_name new_col_definition [FIRST | AFTER col_name];

如修改上表字段name名为uname：

ALTER TABLE tablename CHANGE name uname CHAR(128);

mysql> select * from techer;

+----+------+------+

| id | name | year |

+----+------+------+

|  1 | tom  | NULL |

+----+------+------+

1 row in set (0.00 sec)

mysql> alter table techer change year year1 date;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from techer;

+----+------+-------+

| id | name | year1 |

+----+------+-------+

|  1 | tom  | NULL  |

+----+------+-------+

1 row in set (0.00 sec)

    CHANGE和MODIFY都可以修改表字段定义，不同的是CHANGE写两次字段名，但是CHANGE可以修改列名，而MODIFY则不能。

修改字段排列顺序

    上面介绍的修改表定义后面都有一个可选项目[FIRST | AFTER col_name]，这个选项可以用来修改字段在表中的顺序，ADD新增字段的默认位置是在表的最后，CHANGE和MODIFY默认不改动字段的位置。

    如：

Sql代码  

1. 在上表中添加birth字段，并放到列id后面：  
2. ALTERTABLE tablename ADD birth DATETIME AFTER id;  
3. 再次修改，把它放到uname表后面：  
4. ALTERTABLE tablename MODIFY birth DATETIME NOTNULLAFTER uname;  

在上表中添加birth字段，并放到列id后面：

ALTER TABLE tablename ADD birth DATETIME AFTER id;

再次修改，把它放到uname表后面：

ALTER TABLE tablename MODIFY birth DATETIME NOT NULL AFTER uname;

修改表名

Sql代码  

1. 语法：  
2. ALTERTABLE tablename RENAME [TO] new_tablename;  
3. 如把上面表名tablename改成test：  
4. ALTERTABLE tablename RENAME test;  

语法：

ALTER TABLE tablename RENAME [TO] new_tablename;

如把上面表名tablename改成test：

ALTER TABLE tablename RENAME test;

mysql> alter table techer rename techer1

    -> ;

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

+------------------+

| Tables_in_school |

+------------------+

| techer1          |

+------------------+

1 row in set (0.00 sec)

mysql> CREATE USER 'cat' @ 'localhost' IDENTIFIED BY '775120';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''localhost' IDENTIFIED BY '775120'' at line 1

mysql> CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> CREATE USER 'cat' @ 'localhost' IDENTIFIED BY '775120';

mysql> CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';