2023年6月21日发(作者：)

数据库基础——SQL语⾔与常⽤语句⼀、SQL语⾔1、概述SQL：Structure Query Language（结构化查询语⾔），SQL被美国国家标准局（ANSI）确定为关系型数据库语⾔的美国标准，后来被国际化组织（ISO）采纳为关系数据库语⾔的国际标准。各个数据库⼚商都⽀持ISO的SQL标准（可以理解为我们国家的-普通话）各个数据库⼚商在标准的基础上做了⾃⼰的扩展（可以理解为我们各个地区的-⽅⾔）SQL是⼀种标准化的语⾔，它允许你在数据库上执⾏操作⽐如：创建项⽬、查询内容、更新内容、并删除条⽬等操作。Create，Read，Update，and Delete 通常称为CRUD（增删改查）操作。2、SQL语句分类DDL（Data Defintion Language）：数据定义语⾔，⽤来定义数据库对象：库、表、列等。DML（Data Manipulation Language）：数据操作语⾔，⽤来定义数据库记录（数据）增删改。DCL（Data Control Language）：数据控制语⾔，⽤来定义访问权限和安全级别。DQL（Data Query Language）：数据查询语⾔，⽤来查询记录（数据）。注意：SQL语句以;结尾，MySQL中的关键字不区分⼤⼩写3、DDL操作数据库1）创建CREATE DATABASE 语句⽤于创建新的数据库；数据库的编码⽅式有4种常⽤的：gb2312，

utf-8，

gbk，

iso-8859-1 ;//create database 数据库名;create database mydb1;//create database 数据库名 character set 编码⽅式;create database mydb2 character set gbk;//create database 数据库名 set 编码⽅式 collate 排序规则；create database mydb3 character set gbk collate gbk_chinese_ci;⽰例：mysql> create database mydb1;Query OK, 1 row affected (0.12 sec)mysql> create database mydb2 character set gbk;Query OK, 1 row affected (0.08 sec)mysql> create database mydb3 character set gbk collate gbk_chinese_ci;Query OK, 1 row affected (0.11 sec)2）查看数据库查看当前数据库服务器中的所有数据库show database;查看前⾯创建的mydb2数据库的定义信息show create database 数据库名;show create database mydb2;⽰例：mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mydb1 || mydb2 || mydb3 || mysql || performance_schema || sys |+--------------------+7 rows in set (0.00 sec)mysql> show create database mydb2;+----------+--------------------------------------------------------------------------------------------------+| Database | Create Database |+----------+--------------------------------------------------------------------------------------------------+| mydb2 | CREATE DATABASE `mydb2` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |+----------+--------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)注意：在查看数据库中除了⾃⼰定义的数据库名外，还有⼀些是⾃动⽣成的数据库，这些不⽤管它就⾏，别把它们删除。3）修改数据库alter database 数据库名 character set 编码⽅式；查看服务器中的数据库，并把mydb的字符集修改为utf8;alter database mydb2 character set utf8;⽰例：mysql> alter database mydb2 character set utf8;Query OK, 1 row affected, 1 warning (0.20 sec)mysql> show create database mydb2;+----------+---------------------------------------------------------------------------------------------------+| Database | Create Database |+----------+---------------------------------------------------------------------------------------------------+| mydb2 | CREATE DATABASE `mydb2` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |+----------+---------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)4）删除数据库drop database 数据库名；drop database mydb3;⽰例：mysql> drop database mydb3;Query OK, 0 rows affected (0.10 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mydb1 || mydb2 || mysql || performance_schema || sys |+--------------------+6 rows in set (0.00 sec)5）其他语句查看当前使⽤的数据库selsect database();切换数据库：use 数据库名;use mydb2;⽰例：mysql> select database();+------------+| database() |+------------+| NULL |+------------+1 row in set (0.00 sec)mysql> use mydb2;Database changedmysql> select database();+------------+| database() |+------------+| mydb2 |+------------+1 row in set (0.00 sec)4、DDL操作表1）创建表CREATE TABLE 语句⽤于创建新表语法：create table 表名(列名1 数据类型 [约束],列名2 数据类型 [约束],类名n 数据类型 [约束]);说明：表名、列名都是⾃定义的，多列之间使⽤逗号间隔，最后⼀列的逗号不能写；[约束] 表⽰可有可⽆。⽰例：mysql> create table Employees( -> id INT, -> name VARCHAR(255), -> age INT -> );Query OK, 0 rows affected (1.05 sec)mysql> desc employees;+-------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(255) | YES | | NULL | || age | int | YES | | NULL | |+-------+--------------+------+-----+---------+-------+3 rows in set (0.12 sec)2）常⽤数据类型：> int：整型> double：浮点型，例如double（5，2）表⽰最多5位，其中必须有2位⼩数，即最⼤值为999.99；默认⽀持四舍五⼊；> char：固定长度字符串类型；char(10) 'aaa ' 占10位；> varchar：可变长度字符串类型；varchar(10) 'aaa' 占3位；> text：字符串类型，⽐如：⼩说信息；> blob：字节类型，保存⽂件信息（视频，⾳频，图⽚）；> date：⽇期类型，格式为：yyyy-MM-dd;> time：时间类型，格式为：hh:mm:ss;> timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会⾃动赋值> datetime：⽇期时间类型 yyyy-MM-dd hh:mm:ss3）其他表操作删除表：drop table 表名;⽰例：mysql> create table student( -> class int, -> name varchar(255), -> age int, -> school varchar(255) -> );Query OK, 0 rows affected (0.51 sec)mysql> drop table student;Query OK, 0 rows affected (0.80 sec)展⽰当前数据库中的所有表：show tables;⽰例：mysql> show tables;+-----------------+| Tables_in_mydb2 |+-----------------+| employees |+-----------------+1 row in set (0.00 sec)查看表的字段信息desc 表名;⽰例：mysql> desc employees;+-------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(255) | YES | | NULL | || age | int | YES | | NULL | |+-------+--------------+------+-----+---------+-------+3 rows in set (0.00 sec)增加列：在上⾯员⼯表的基本上增加⼀个image列。alter table 表名 add 新列名 新的数据类型;⽰例：alter table employees add image blob;mysql> alter table employees add image blob;Query OK, 0 rows affected (0.72 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc employees;+-------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(255) | YES | | NULL | || age | int | YES | | NULL | || image | blob | YES | | NULL | |+-------+--------------+------+-----+---------+-------+4 rows in set (0.00 sec)修改name列，使其长度为60alter table 表名 modify 列名 新的数据类型;alter table 表名 change 旧列名 新列名 新的数据类型;⽰例：alter table employees modify name varchar(60);mysql> alter table employees modify name varchar(60);Query OK, 0 rows affected (1.17 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc employees;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(60) | YES | | NULL | || age | int | YES | | NULL | || image | blob | YES | | NULL | |+-------+-------------+------+-----+---------+-------+4 rows in set (0.01 sec)alter table employees change name name1 varchar(50);mysql> alter table employees change name name1 varchar(50);Query OK, 0 rows affected (0.19 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc employees;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name1 | varchar(50) | YES | | NULL | || age | int | YES | | NULL | || image | blob | YES | | NULL | |+-------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)删除image列，⼀次只能删⼀列alter table 表名 drop 列名⽰例：alter table employees drop image;mysql> alter table employees drop image;Query OK, 0 rows affected (0.94 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc employees;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name1 | varchar(50) | YES | | NULL | || age | int | YES | | NULL | |+-------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)修改表名，表名为studentalter table 旧表名 rename 新表名;⽰例：alter table employees rename student;mysql> alter table employees rename student;Query OK, 0 rows affected (0.50 sec)mysql> show tables;+-----------------+| Tables_in_mydb2 |+-----------------+| student |+-----------------+1 row in set (0.00 sec)查看表格的创建细节show create table 表名;⽰例：show create table student;mysql> show create table student;+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| student | CREATE TABLE `student` ( `id` int DEFAULT NULL, `name1` varchar(50) DEFAULT NULL, `age` int DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)修改表的字符集为gbkalter table 表名 character set 编码⽅式;⽰例：alter table student character set gbk;mysql> alter table student character set gbk;Query OK, 0 rows affected (0.14 sec)Records: 0 Duplicates: 0 Warnings: 05、DML操作数据DML是对表中的数据进⾏增、删、改的操作。主要包括:INSERT、UPDATE、DELETE在mysql中，字符串类型和⽇期类型都要⽤''单引号括起来。空值：null1）插⼊操作：INSERTinsert into 表名(列名) values(数据值);⽰例：insert inoto student (id,name,age,birthday) values(1001,'张三',18,'2003-8-20');mysql> insert into student(id,name,age,birthday) -> values(1001,'张三',18,'2003-8-20');Query OK, 1 row affected (0.29 sec)注意：1. 多列和多个列值之间使⽤逗号隔开2. 列名要和列值⼀⼀对应3. ⾮数值的列值两侧需要加单引号添加数据的时候可以将列名省略（当给所有列添加数据的时候）此时列值的顺序按照数据表中列的顺序执⾏⽰例：insert into student values(1002,'李四',19,'2004-9-1');mysql> insert into student values(1002,'李四',19,'2004-9-1');Query OK, 1 row affected (0.12 sec)同时添加多⾏insert into 表名(列名) values(第⼀⾏数据)(第⼆⾏数据)()…(第n⾏数据);⽰例：mysql> insert into student(id,name,age,birthday) -> values -> (1003,'西⽠',20,'2005-1-1'), -> (1004,'⼟⾖',21,'2020-1-1'), -> (1005,'红薯',22,'2021-1-1');Query OK, 3 rows affected (0.17 sec)Records: 3 Duplicates: 0 Warnings: 0注意：列名与列值的类型、个数、顺序要⼀⼀对应。参数值不要超出列定义的长度。如果插⼊空值，请使⽤null插⼊的⽇期和字符⼀样，都需要使⽤单引号括起来。查询数据查询所有数据：select*from 表名;mysql> select*from student;+------+--------+------+------------+| id | name | age | birthday |+------+--------+------+------------+| 1001 | 张三 | 18 | 2003-08-20 || 1002 | 李四 | 19 | 2004-09-01 || 1003 | 西⽠ | 20 | 2005-01-01 || 1004 | ⼟⾖ | 21 | 2020-01-01 || 1005 | 红薯 | 22 | 2021-01-01 |+------+--------+------+------------+5 rows in set (0.00 sec)2）修改操作：UPDATE语法：UPDATE 表名 SET 列名1=列值1，列名2=列值2, … WHERE 列名=值;⽰例1：将所有id改为1010update student set id=1010;mysql> update student set id=1010;Query OK, 5 rows affected (0.10 sec)Rows matched: 5 Changed: 5 Warnings: 0mysql> select*from student -> ;+------+--------+------+------------+| id | name | age | birthday |+------+--------+------+------------+| 1010 | 张三 | 18 | 2003-08-20 || 1010 | 李四 | 19 | 2004-09-01 || 1010 | 西⽠ | 20 | 2005-01-01 || 1010 | ⼟⾖ | 21 | 2020-01-01 || 1010 | 红薯 | 22 | 2021-01-01 |+------+--------+------+------------+5 rows in set (0.00 sec)⽰例2：将’李四’的id改为9758。update student set id=9758 where name='李四';mysql> update student set id=9758 where name='李四';Query OK, 1 row affected (0.05 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select*from student;+------+--------+------+------------+| id | name | age | birthday |+------+--------+------+------------+| 1010 | 张三 | 18 | 2003-08-20 || 9758 | 李四 | 19 | 2004-09-01 || 1010 | 西⽠ | 20 | 2005-01-01 || 1010 | ⼟⾖ | 21 | 2020-01-01 || 1010 | 红薯 | 22 | 2021-01-01 |+------+--------+------+------------+5 rows in set (0.00 sec)⽰例3：将‘⼟⾖’的id改为7777，age改为99update student set id=7777,age=99 where name='⼟⾖';mysql> update student set id=7777,age=99 where name='⼟⾖';Query OK, 1 row affected (0.15 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select*from student;+------+--------+------+------------+| id | name | age | birthday |+------+--------+------+------------+| 1010 | 张三 | 18 | 2003-08-20 || 9758 | 李四 | 19 | 2004-09-01 || 1010 | 西⽠ | 20 | 2005-01-01 || 7777 | ⼟⾖ | 99 | 2020-01-01 || 1010 | 红薯 | 22 | 2021-01-01 |+------+--------+------+------------+5 rows in set (0.00 sec)⽰例4：将‘红薯’的id在原有基础上增加1000update student set id=id+1000 where name='红薯';mysql> update student set id=id+1000 where name='红薯';Query OK, 1 row affected (0.26 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select*from student;+------+--------+------+------------+| id | name | age | birthday |+------+--------+------+------------+| 1010 | 张三 | 18 | 2003-08-20 || 9758 | 李四 | 19 | 2004-09-01 || 1010 | 西⽠ | 20 | 2005-01-01 || 7777 | ⼟⾖ | 99 | 2020-01-01 || 2010 | 红薯 | 22 | 2021-01-01 |+------+--------+------+------------+5 rows in set (0.00 sec)3）删除操作：DELETE语法：DELETE from 表名【WHERE 列名=值】⽰例1：删除表中名称‘西⽠’的记录delete from student where name='西⽠';mysql> delete from student where name='西⽠';Query OK, 1 row affected (0.18 sec)mysql> select*from student;+------+--------+------+------------+| id | name | age | birthday |+------+--------+------+------------+| 1010 | 张三 | 18 | 2003-08-20 || 9758 | 李四 | 19 | 2004-09-01 || 7777 | ⼟⾖ | 99 | 2020-01-01 || 2010 | 红薯 | 22 | 2021-01-01 |+------+--------+------+------------+4 rows in set (0.00 sec)⽰例 2：删除表中所有记录。delete from student;mysql> delete from student;Query OK, 4 rows affected (0.07 sec)mysql> select*from student;Empty set (0.00 sec)⽰例3：使⽤truncate删除表中记录。truncate table student;mysql> truncate table student;Query OK, 0 rows affected (0.87 sec)注意：1.

delete删除表中的数据，表结构还在；删除后的数据可以找回。2.

truncate删除是把表直接drop掉，然后再创建⼀个同样的新表；删除的数据不能找回，执⾏速度⽐delete快。6、DCL控制权限1）创建⽤户语法1：create user ⽤户名@指定ip identified by 密码;create user test123@localhost identified by '123456';语法2：create user test456@客户端ip identified by 密码;（指定IP才能登录）create user test456@10.4.10.18 identified by '654321';语法3：create user ⽤户名@’%’ identified by 密码;（任意IP均可登录）create user test789@'%' identified by '987654';⽰例：mysql> create user test123@localhost identified by '123456';Query OK, 0 rows affected (0.29 sec)mysql> create user test456@10.4.10.18 identified by '654321';Query OK, 0 rows affected (0.15 sec)mysql> create user test789@'%' identified by '987654';Query OK, 0 rows affected (0.25 sec)2）⽤户授权grant 权限1，权限2，… ，权限n on数据库名.* to ⽤户名@IP;（给指定⽤户授予指定数据库和权限）grant select,insert,update,delete,create on chaoshi.* to 'test456'@'127.0.0.1';grant all on .to ⽤户名@IP （给指定⽤户授予所有数据库权限）grant all on *.* to 'test456'@'127.0.0.1';⽰例：mysql> grant select,insert,update,delete,create on mydb1.* to 'test123'@'localhost';Query OK, 0 rows affected (0.11 sec)mysql> grant all on *.* to 'test456'@'10.4.10.18';Query OK, 0 rows affected (0.09 sec)3）⽤户权限查询show grants for ⽤户名@IP;show grants for 'root'@'%';⽰例：mysql> show grants for 'test123'@'localhost';+------------------------------------------------------------------------------------+| Grants for test123@localhost |+------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO `test123`@`localhost` || GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `mydb1`.* TO `test123`@`localhost` |+------------------------------------------------------------------------------------+2 rows in set (0.00 sec)4）撤销⽤户权限revoke 权限1，权限2，…，权限n on 数据库名.* from ⽤户名@IP;revoke select on *.* from 'root'@'%';⽰例：mysql> revoke select on mydb1.* from 'test123'@'localhost';Query OK, 0 rows affected (0.06 sec)mysql> show grants for 'test123'@'localhost';+----------------------------------------------------------------------------+| Grants for test123@localhost |+----------------------------------------------------------------------------+| GRANT USAGE ON *.* TO `test123`@`localhost` || GRANT INSERT, UPDATE, DELETE, CREATE ON `mydb1`.* TO `test123`@`localhost` |+----------------------------------------------------------------------------+2 rows in set (0.00 sec)可以观察到，权限中少了SELECT5）删除⽤户drop user ⽤户名@IP;drop user test123@localhost;⽰例：删除掉⽤户后，再次查询该⽤户权限，系统提⽰找不到该⽤户。mysql> drop user 'test123'@'localhost';Query OK, 0 rows affected (0.06 sec)mysql> show grants for 'test123'@'localhost';ERROR 1141 (42000): There is no such grant defined for user 'test123' on host 'localhost'练习练习题:Manager(管理员表)： mid 编号 int （主键） mname 名字 varchar(20) age 年龄 int sex 性别 char(2) password 密码 varchar(20) address 地址 varchar(20) phone 电话 varchar(20)数据： 1 王⼦ 18 男 123 北京 1101 2 公主 20 ⼥ 456 上海 2202 3 太⼦ 23 男 789 南京 3303需求： (1)创建表 (2)将数据插⼊到表中 (3)将王⼦的年龄修改为24 (4)将地址是南京的管理员改为天津 (5)将性别是⼥,并且年龄⼤于30的⽤户密码改为888888 (6)将所有⽤户的密码恢复最初设置111111 (7)将员⼯的电话中不是1101的电话号码改为7654321

(8)将王⼦的年龄改为18，地址改为承德，性别改为⼥ (9) 删除王⼦的信息 (10)删除地址在南京并且年龄⼤于60的员⼯信息 (11)删除不在北京的员⼯信息 (12)删除地址在北京或上海的员⼯信息

(13)删除电话号码是空的员⼯信息

语句：（1）mysql> create table manager( -> mid int primary key, -> mname varchar(20), -> age int, -> sex char(2), -> password varchar(20), -> address varchar(20), -> phone varchar(20) -> );（2）mysql> insert into manager(mid,mname,age,sex,password,address,phone) -> values -> (1,'王⼦',18,'男','123','北京','1101'), -> (2,'公主',20,'⼥','456','上海','2202'), -> (3,'太⼦',23,'男','789','南京','3303');（3）mysql> update manager set age=24 where mname='王⼦';（4）mysql> update manager set address='天津' where address='南京';（5）mysql> update manager set password=888888 where sex='⼥' and age>30;（6）mysql> update manager set password=111111;（7）mysql> update manager set phone=7654321 where phone!=1101;（8）mysql> update manager set age=18,address='承德',sex='⼥' where mname='王⼦';（9）mysql> delete from manager where mname='王⼦';（10）mysql> delete from manager where address='南京' and age>60;（11）mysql> delete from manager where address!='北京';（12）mysql> delete from manager where address='北京' or address='上海';（13) mysql> delete from manager where phone='';由于篇幅过⼤，本篇就先到这⾥。关于DQL数据查询的内容请关注下⼀篇⽂章：

2023年6月21日发(作者：)

数据库基础——SQL语⾔与常⽤语句⼀、SQL语⾔1、概述SQL：Structure Query Language（结构化查询语⾔），SQL被美国国家标准局（ANSI）确定为关系型数据库语⾔的美国标准，后来被国际化组织（ISO）采纳为关系数据库语⾔的国际标准。各个数据库⼚商都⽀持ISO的SQL标准（可以理解为我们国家的-普通话）各个数据库⼚商在标准的基础上做了⾃⼰的扩展（可以理解为我们各个地区的-⽅⾔）SQL是⼀种标准化的语⾔，它允许你在数据库上执⾏操作⽐如：创建项⽬、查询内容、更新内容、并删除条⽬等操作。Create，Read，Update，and Delete 通常称为CRUD（增删改查）操作。2、SQL语句分类DDL（Data Defintion Language）：数据定义语⾔，⽤来定义数据库对象：库、表、列等。DML（Data Manipulation Language）：数据操作语⾔，⽤来定义数据库记录（数据）增删改。DCL（Data Control Language）：数据控制语⾔，⽤来定义访问权限和安全级别。DQL（Data Query Language）：数据查询语⾔，⽤来查询记录（数据）。注意：SQL语句以;结尾，MySQL中的关键字不区分⼤⼩写3、DDL操作数据库1）创建CREATE DATABASE 语句⽤于创建新的数据库；数据库的编码⽅式有4种常⽤的：gb2312，

utf-8，

gbk，

iso-8859-1 ;//create database 数据库名;create database mydb1;//create database 数据库名 character set 编码⽅式;create database mydb2 character set gbk;//create database 数据库名 set 编码⽅式 collate 排序规则；create database mydb3 character set gbk collate gbk_chinese_ci;⽰例：mysql> create database mydb1;Query OK, 1 row affected (0.12 sec)mysql> create database mydb2 character set gbk;Query OK, 1 row affected (0.08 sec)mysql> create database mydb3 character set gbk collate gbk_chinese_ci;Query OK, 1 row affected (0.11 sec)2）查看数据库查看当前数据库服务器中的所有数据库show database;查看前⾯创建的mydb2数据库的定义信息show create database 数据库名;show create database mydb2;⽰例：mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mydb1 || mydb2 || mydb3 || mysql || performance_schema || sys |+--------------------+7 rows in set (0.00 sec)mysql> show create database mydb2;+----------+--------------------------------------------------------------------------------------------------+| Database | Create Database |+----------+--------------------------------------------------------------------------------------------------+| mydb2 | CREATE DATABASE `mydb2` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |+----------+--------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)注意：在查看数据库中除了⾃⼰定义的数据库名外，还有⼀些是⾃动⽣成的数据库，这些不⽤管它就⾏，别把它们删除。3）修改数据库alter database 数据库名 character set 编码⽅式；查看服务器中的数据库，并把mydb的字符集修改为utf8;alter database mydb2 character set utf8;⽰例：mysql> alter database mydb2 character set utf8;Query OK, 1 row affected, 1 warning (0.20 sec)mysql> show create database mydb2;+----------+---------------------------------------------------------------------------------------------------+| Database | Create Database |+----------+---------------------------------------------------------------------------------------------------+| mydb2 | CREATE DATABASE `mydb2` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |+----------+---------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)4）删除数据库drop database 数据库名；drop database mydb3;⽰例：mysql> drop database mydb3;Query OK, 0 rows affected (0.10 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mydb1 || mydb2 || mysql || performance_schema || sys |+--------------------+6 rows in set (0.00 sec)5）其他语句查看当前使⽤的数据库selsect database();切换数据库：use 数据库名;use mydb2;⽰例：mysql> select database();+------------+| database() |+------------+| NULL |+------------+1 row in set (0.00 sec)mysql> use mydb2;Database changedmysql> select database();+------------+| database() |+------------+| mydb2 |+------------+1 row in set (0.00 sec)4、DDL操作表1）创建表CREATE TABLE 语句⽤于创建新表语法：create table 表名(列名1 数据类型 [约束],列名2 数据类型 [约束],类名n 数据类型 [约束]);说明：表名、列名都是⾃定义的，多列之间使⽤逗号间隔，最后⼀列的逗号不能写；[约束] 表⽰可有可⽆。⽰例：mysql> create table Employees( -> id INT, -> name VARCHAR(255), -> age INT -> );Query OK, 0 rows affected (1.05 sec)mysql> desc employees;+-------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(255) | YES | | NULL | || age | int | YES | | NULL | |+-------+--------------+------+-----+---------+-------+3 rows in set (0.12 sec)2）常⽤数据类型：> int：整型> double：浮点型，例如double（5，2）表⽰最多5位，其中必须有2位⼩数，即最⼤值为999.99；默认⽀持四舍五⼊；> char：固定长度字符串类型；char(10) 'aaa ' 占10位；> varchar：可变长度字符串类型；varchar(10) 'aaa' 占3位；> text：字符串类型，⽐如：⼩说信息；> blob：字节类型，保存⽂件信息（视频，⾳频，图⽚）；> date：⽇期类型，格式为：yyyy-MM-dd;> time：时间类型，格式为：hh:mm:ss;> timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会⾃动赋值> datetime：⽇期时间类型 yyyy-MM-dd hh:mm:ss3）其他表操作删除表：drop table 表名;⽰例：mysql> create table student( -> class int, -> name varchar(255), -> age int, -> school varchar(255) -> );Query OK, 0 rows affected (0.51 sec)mysql> drop table student;Query OK, 0 rows affected (0.80 sec)展⽰当前数据库中的所有表：show tables;⽰例：mysql> show tables;+-----------------+| Tables_in_mydb2 |+-----------------+| employees |+-----------------+1 row in set (0.00 sec)查看表的字段信息desc 表名;⽰例：mysql> desc employees;+-------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(255) | YES | | NULL | || age | int | YES | | NULL | |+-------+--------------+------+-----+---------+-------+3 rows in set (0.00 sec)增加列：在上⾯员⼯表的基本上增加⼀个image列。alter table 表名 add 新列名 新的数据类型;⽰例：alter table employees add image blob;mysql> alter table employees add image blob;Query OK, 0 rows affected (0.72 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc employees;+-------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(255) | YES | | NULL | || age | int | YES | | NULL | || image | blob | YES | | NULL | |+-------+--------------+------+-----+---------+-------+4 rows in set (0.00 sec)修改name列，使其长度为60alter table 表名 modify 列名 新的数据类型;alter table 表名 change 旧列名 新列名 新的数据类型;⽰例：alter table employees modify name varchar(60);mysql> alter table employees modify name varchar(60);Query OK, 0 rows affected (1.17 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc employees;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(60) | YES | | NULL | || age | int | YES | | NULL | || image | blob | YES | | NULL | |+-------+-------------+------+-----+---------+-------+4 rows in set (0.01 sec)alter table employees change name name1 varchar(50);mysql> alter table employees change name name1 varchar(50);Query OK, 0 rows affected (0.19 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc employees;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name1 | varchar(50) | YES | | NULL | || age | int | YES | | NULL | || image | blob | YES | | NULL | |+-------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)删除image列，⼀次只能删⼀列alter table 表名 drop 列名⽰例：alter table employees drop image;mysql> alter table employees drop image;Query OK, 0 rows affected (0.94 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc employees;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name1 | varchar(50) | YES | | NULL | || age | int | YES | | NULL | |+-------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)修改表名，表名为studentalter table 旧表名 rename 新表名;⽰例：alter table employees rename student;mysql> alter table employees rename student;Query OK, 0 rows affected (0.50 sec)mysql> show tables;+-----------------+| Tables_in_mydb2 |+-----------------+| student |+-----------------+1 row in set (0.00 sec)查看表格的创建细节show create table 表名;⽰例：show create table student;mysql> show create table student;+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| student | CREATE TABLE `student` ( `id` int DEFAULT NULL, `name1` varchar(50) DEFAULT NULL, `age` int DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)修改表的字符集为gbkalter table 表名 character set 编码⽅式;⽰例：alter table student character set gbk;mysql> alter table student character set gbk;Query OK, 0 rows affected (0.14 sec)Records: 0 Duplicates: 0 Warnings: 05、DML操作数据DML是对表中的数据进⾏增、删、改的操作。主要包括:INSERT、UPDATE、DELETE在mysql中，字符串类型和⽇期类型都要⽤''单引号括起来。空值：null1）插⼊操作：INSERTinsert into 表名(列名) values(数据值);⽰例：insert inoto student (id,name,age,birthday) values(1001,'张三',18,'2003-8-20');mysql> insert into student(id,name,age,birthday) -> values(1001,'张三',18,'2003-8-20');Query OK, 1 row affected (0.29 sec)注意：1. 多列和多个列值之间使⽤逗号隔开2. 列名要和列值⼀⼀对应3. ⾮数值的列值两侧需要加单引号添加数据的时候可以将列名省略（当给所有列添加数据的时候）此时列值的顺序按照数据表中列的顺序执⾏⽰例：insert into student values(1002,'李四',19,'2004-9-1');mysql> insert into student values(1002,'李四',19,'2004-9-1');Query OK, 1 row affected (0.12 sec)同时添加多⾏insert into 表名(列名) values(第⼀⾏数据)(第⼆⾏数据)()…(第n⾏数据);⽰例：mysql> insert into student(id,name,age,birthday) -> values -> (1003,'西⽠',20,'2005-1-1'), -> (1004,'⼟⾖',21,'2020-1-1'), -> (1005,'红薯',22,'2021-1-1');Query OK, 3 rows affected (0.17 sec)Records: 3 Duplicates: 0 Warnings: 0注意：列名与列值的类型、个数、顺序要⼀⼀对应。参数值不要超出列定义的长度。如果插⼊空值，请使⽤null插⼊的⽇期和字符⼀样，都需要使⽤单引号括起来。查询数据查询所有数据：select*from 表名;mysql> select*from student;+------+--------+------+------------+| id | name | age | birthday |+------+--------+------+------------+| 1001 | 张三 | 18 | 2003-08-20 || 1002 | 李四 | 19 | 2004-09-01 || 1003 | 西⽠ | 20 | 2005-01-01 || 1004 | ⼟⾖ | 21 | 2020-01-01 || 1005 | 红薯 | 22 | 2021-01-01 |+------+--------+------+------------+5 rows in set (0.00 sec)2）修改操作：UPDATE语法：UPDATE 表名 SET 列名1=列值1，列名2=列值2, … WHERE 列名=值;⽰例1：将所有id改为1010update student set id=1010;mysql> update student set id=1010;Query OK, 5 rows affected (0.10 sec)Rows matched: 5 Changed: 5 Warnings: 0mysql> select*from student -> ;+------+--------+------+------------+| id | name | age | birthday |+------+--------+------+------------+| 1010 | 张三 | 18 | 2003-08-20 || 1010 | 李四 | 19 | 2004-09-01 || 1010 | 西⽠ | 20 | 2005-01-01 || 1010 | ⼟⾖ | 21 | 2020-01-01 || 1010 | 红薯 | 22 | 2021-01-01 |+------+--------+------+------------+5 rows in set (0.00 sec)⽰例2：将’李四’的id改为9758。update student set id=9758 where name='李四';mysql> update student set id=9758 where name='李四';Query OK, 1 row affected (0.05 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select*from student;+------+--------+------+------------+| id | name | age | birthday |+------+--------+------+------------+| 1010 | 张三 | 18 | 2003-08-20 || 9758 | 李四 | 19 | 2004-09-01 || 1010 | 西⽠ | 20 | 2005-01-01 || 1010 | ⼟⾖ | 21 | 2020-01-01 || 1010 | 红薯 | 22 | 2021-01-01 |+------+--------+------+------------+5 rows in set (0.00 sec)⽰例3：将‘⼟⾖’的id改为7777，age改为99update student set id=7777,age=99 where name='⼟⾖';mysql> update student set id=7777,age=99 where name='⼟⾖';Query OK, 1 row affected (0.15 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select*from student;+------+--------+------+------------+| id | name | age | birthday |+------+--------+------+------------+| 1010 | 张三 | 18 | 2003-08-20 || 9758 | 李四 | 19 | 2004-09-01 || 1010 | 西⽠ | 20 | 2005-01-01 || 7777 | ⼟⾖ | 99 | 2020-01-01 || 1010 | 红薯 | 22 | 2021-01-01 |+------+--------+------+------------+5 rows in set (0.00 sec)⽰例4：将‘红薯’的id在原有基础上增加1000update student set id=id+1000 where name='红薯';mysql> update student set id=id+1000 where name='红薯';Query OK, 1 row affected (0.26 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select*from student;+------+--------+------+------------+| id | name | age | birthday |+------+--------+------+------------+| 1010 | 张三 | 18 | 2003-08-20 || 9758 | 李四 | 19 | 2004-09-01 || 1010 | 西⽠ | 20 | 2005-01-01 || 7777 | ⼟⾖ | 99 | 2020-01-01 || 2010 | 红薯 | 22 | 2021-01-01 |+------+--------+------+------------+5 rows in set (0.00 sec)3）删除操作：DELETE语法：DELETE from 表名【WHERE 列名=值】⽰例1：删除表中名称‘西⽠’的记录delete from student where name='西⽠';mysql> delete from student where name='西⽠';Query OK, 1 row affected (0.18 sec)mysql> select*from student;+------+--------+------+------------+| id | name | age | birthday |+------+--------+------+------------+| 1010 | 张三 | 18 | 2003-08-20 || 9758 | 李四 | 19 | 2004-09-01 || 7777 | ⼟⾖ | 99 | 2020-01-01 || 2010 | 红薯 | 22 | 2021-01-01 |+------+--------+------+------------+4 rows in set (0.00 sec)⽰例 2：删除表中所有记录。delete from student;mysql> delete from student;Query OK, 4 rows affected (0.07 sec)mysql> select*from student;Empty set (0.00 sec)⽰例3：使⽤truncate删除表中记录。truncate table student;mysql> truncate table student;Query OK, 0 rows affected (0.87 sec)注意：1.

delete删除表中的数据，表结构还在；删除后的数据可以找回。2.

truncate删除是把表直接drop掉，然后再创建⼀个同样的新表；删除的数据不能找回，执⾏速度⽐delete快。6、DCL控制权限1）创建⽤户语法1：create user ⽤户名@指定ip identified by 密码;create user test123@localhost identified by '123456';语法2：create user test456@客户端ip identified by 密码;（指定IP才能登录）create user test456@10.4.10.18 identified by '654321';语法3：create user ⽤户名@’%’ identified by 密码;（任意IP均可登录）create user test789@'%' identified by '987654';⽰例：mysql> create user test123@localhost identified by '123456';Query OK, 0 rows affected (0.29 sec)mysql> create user test456@10.4.10.18 identified by '654321';Query OK, 0 rows affected (0.15 sec)mysql> create user test789@'%' identified by '987654';Query OK, 0 rows affected (0.25 sec)2）⽤户授权grant 权限1，权限2，… ，权限n on数据库名.* to ⽤户名@IP;（给指定⽤户授予指定数据库和权限）grant select,insert,update,delete,create on chaoshi.* to 'test456'@'127.0.0.1';grant all on .to ⽤户名@IP （给指定⽤户授予所有数据库权限）grant all on *.* to 'test456'@'127.0.0.1';⽰例：mysql> grant select,insert,update,delete,create on mydb1.* to 'test123'@'localhost';Query OK, 0 rows affected (0.11 sec)mysql> grant all on *.* to 'test456'@'10.4.10.18';Query OK, 0 rows affected (0.09 sec)3）⽤户权限查询show grants for ⽤户名@IP;show grants for 'root'@'%';⽰例：mysql> show grants for 'test123'@'localhost';+------------------------------------------------------------------------------------+| Grants for test123@localhost |+------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO `test123`@`localhost` || GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `mydb1`.* TO `test123`@`localhost` |+------------------------------------------------------------------------------------+2 rows in set (0.00 sec)4）撤销⽤户权限revoke 权限1，权限2，…，权限n on 数据库名.* from ⽤户名@IP;revoke select on *.* from 'root'@'%';⽰例：mysql> revoke select on mydb1.* from 'test123'@'localhost';Query OK, 0 rows affected (0.06 sec)mysql> show grants for 'test123'@'localhost';+----------------------------------------------------------------------------+| Grants for test123@localhost |+----------------------------------------------------------------------------+| GRANT USAGE ON *.* TO `test123`@`localhost` || GRANT INSERT, UPDATE, DELETE, CREATE ON `mydb1`.* TO `test123`@`localhost` |+----------------------------------------------------------------------------+2 rows in set (0.00 sec)可以观察到，权限中少了SELECT5）删除⽤户drop user ⽤户名@IP;drop user test123@localhost;⽰例：删除掉⽤户后，再次查询该⽤户权限，系统提⽰找不到该⽤户。mysql> drop user 'test123'@'localhost';Query OK, 0 rows affected (0.06 sec)mysql> show grants for 'test123'@'localhost';ERROR 1141 (42000): There is no such grant defined for user 'test123' on host 'localhost'练习练习题:Manager(管理员表)： mid 编号 int （主键） mname 名字 varchar(20) age 年龄 int sex 性别 char(2) password 密码 varchar(20) address 地址 varchar(20) phone 电话 varchar(20)数据： 1 王⼦ 18 男 123 北京 1101 2 公主 20 ⼥ 456 上海 2202 3 太⼦ 23 男 789 南京 3303需求： (1)创建表 (2)将数据插⼊到表中 (3)将王⼦的年龄修改为24 (4)将地址是南京的管理员改为天津 (5)将性别是⼥,并且年龄⼤于30的⽤户密码改为888888 (6)将所有⽤户的密码恢复最初设置111111 (7)将员⼯的电话中不是1101的电话号码改为7654321

(8)将王⼦的年龄改为18，地址改为承德，性别改为⼥ (9) 删除王⼦的信息 (10)删除地址在南京并且年龄⼤于60的员⼯信息 (11)删除不在北京的员⼯信息 (12)删除地址在北京或上海的员⼯信息

(13)删除电话号码是空的员⼯信息

语句：（1）mysql> create table manager( -> mid int primary key, -> mname varchar(20), -> age int, -> sex char(2), -> password varchar(20), -> address varchar(20), -> phone varchar(20) -> );（2）mysql> insert into manager(mid,mname,age,sex,password,address,phone) -> values -> (1,'王⼦',18,'男','123','北京','1101'), -> (2,'公主',20,'⼥','456','上海','2202'), -> (3,'太⼦',23,'男','789','南京','3303');（3）mysql> update manager set age=24 where mname='王⼦';（4）mysql> update manager set address='天津' where address='南京';（5）mysql> update manager set password=888888 where sex='⼥' and age>30;（6）mysql> update manager set password=111111;（7）mysql> update manager set phone=7654321 where phone!=1101;（8）mysql> update manager set age=18,address='承德',sex='⼥' where mname='王⼦';（9）mysql> delete from manager where mname='王⼦';（10）mysql> delete from manager where address='南京' and age>60;（11）mysql> delete from manager where address!='北京';（12）mysql> delete from manager where address='北京' or address='上海';（13) mysql> delete from manager where phone='';由于篇幅过⼤，本篇就先到这⾥。关于DQL数据查询的内容请关注下⼀篇⽂章：