MySQL必会必知笔记
序言：阅读并实践Ben Forta的MySQL必知必会做的笔记
一. 查询
1.简单使用
[root@mysql ~]# mysql -uroot -p123 < hellodb_innodb.sql 所有命令都是在hellodb库中执行，此库可以在MySQL官网下载
进入库 [root@mysql ~]# mysql -u root -p123 查询所有的库 mysql> show databases; 进入库 mysql> use hellodb; 查询库里的所有表 mysql> show tables;
2.检索数据
2.1检索classes表的所有列 mysql> select * from classes;
mysql> select * from classes; +---------+----------------+----------+ | ClassID | Class | NumOfStu | +---------+----------------+----------+ | 1 | Shaolin Pai | 10 | | 2 | Emei Pai | 7 | | 3 | QingCheng Pai | 11 | | 4 | Wudang Pai | 12 | | 5 | Riyue Shenjiao | 31 | | 6 | Lianshan Pai | 27 | | 7 | Ming Jiao | 27 | | 8 | Xiaoyao Pai | 15 | +---------+----------------+----------+ 8 rows in set (0.00 sec)
2.2检索单个列 mysql> select ClassID from classes;
mysql> select ClassID from classes; +---------+ | ClassID | +---------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | +---------+ 8 rows in set (0.00 sec)
2.3 检索多个列 ,检索不同行，使用distinct关键字
mysql> select distinct ClassID from students;
mysql> select distinct ClassID from students; +---------+ | ClassID | +---------+ | 2 | | 1 | | 4 | | 3 | | 5 | | 7 | | 6 | | NULL | +---------+ 8 rows in set (0.00 sec)
3.限制结果，使用limit子句
3.1检索students表ClassID列的前五行； mysql> select ClassID from students limit 5;
mysql> select ClassID from students limit 5; +---------+ | ClassID | +---------+ | 2 | | 1 | | 2 | | 4 | | 3 | +---------+ 5 rows in set (0.00 sec)
3.2检索students表ClassID列，从第五行开始的10行；
mysql> select ClassID from students limit 5,10;
1 mysql> select ClassID from students limit 5,10; 2 +---------+ 3 | ClassID | 4 +---------+ 5 | 5 | 6 | 3 | 7 | 7 | 8 | 6 | 9 | 3 | 10 | 6 | 11 | 1 | 12 | 2 | 13 | 3 | 14 | 4 | 15 +---------+ 16 10 rows in set (0.00 sec)
3.3使用完全限定的表名
mysql> select students.ClassID from students;
mysql> select students.ClassID from students; +---------+ | ClassID | +---------+ | 2 | | 1 | | 2 | | 4 | | 3 | | 5 | | 3 | | 7 | | 6 | | 3 | | 6 | | 1 | | 2 | | 3 | | 4 | | 1 | | 4 | | 7 | | 6 | | 7 | | 6 | | 1 | | 4 | | NULL | | NULL | +---------+ 25 rows in set (0.00 sec)
3.3.1表名完全限定
mysql> select students.ClassID from hellodb.students;
mysql> select students.ClassID from hellodb.students; +---------+ | ClassID | +---------+ | 2 | | 1 | | 2 | | 4 | | 3 | | 5 | | 3 | | 7 | | 6 | | 3 | | 6 | | 1 | | 2 | | 3 | | 4 | | 1 | | 4 | | 7 | | 6 | | 7 | | 6 | | 1 | | 4 | | NULL | | NULL | +---------+ 25 rows in set (0.00 sec)
4.排序检索数据
order by子句,取一个列或多个列，据此对输出进行排序 4.1单列排序 mysql> select ClassID from students order by ClassID;
mysql> select ClassID from students order by ClassID; +---------+ | ClassID | +---------+ | NULL | | NULL | | 1 | | 1 | | 1 | | 1 | | 2 | | 2 | | 2 | | 3 | | 3 | | 3 | | 3 | | 4 | | 4 | | 4 | | 4 | | 5 | | 6 | | 6 | | 6 | | 6 | | 7 | | 7 | | 7 | +---------+ 25 rows in set (0.00 sec)
4.2多列排序
mysql> select Name,ClassID from students order by Name,ClassID;
mysql> select Name,ClassID from students order by Name,ClassID; +---------------+---------+ | Name | ClassID | +---------------+---------+ | Diao Chan | 7 | | Ding Dian | 4 | | Duan Yu | 4 | | Hua Rong | 7 | | Huang Yueying | 6 | | Lin Chong | 4 | | Lin Daiyu | 7 | | Lu Wushuang | 3 | | Ma Chao | 4 | | Ren Yingying | 6 | | Shi Potian | 1 | | Shi Qing | 5 | | Shi Zhongyu | 2 | | Sun Dasheng | NULL | | Tian Boguang | 2 | | Wen Qingqing | 1 | | Xi Ren | 3 | | Xiao Qiao | 1 | | Xie Yanke | 2 | | Xu Xian | NULL | | Xu Zhu | 1 | | Xue Baochai | 6 | | Yu Yutong | 3 | | Yuan Chengzhi | 6 | | Yue Lingshan | 3 | +---------------+---------+ 25 rows in set (0.00 sec)
4.3指定排序方向
降序 desc mysql> select ClassID from students order by ClassID desc;
mysql> select ClassID from students order by ClassID desc; +---------+ | ClassID | +---------+ | 7 | | 7 | | 7 | | 6 | | 6 | | 6 | | 6 | | 5 | | 4 | | 4 | | 4 | | 4 | | 3 | | 3 | | 3 | | 3 | | 2 | | 2 | | 2 | | 1 | | 1 | | 1 | | 1 | | NULL | | NULL | +---------+ 25 rows in set (0.00 sec)
升序asc mysql> select ClassID from students order by ClassID asc;
mysql> select ClassID from students order by ClassID asc; +---------+ | ClassID | +---------+ | NULL | | NULL | | 1 | | 1 | | 1 | | 1 | | 2 | | 2 | | 2 | | 3 | | 3 | | 3 | | 3 | | 4 | | 4 | | 4 | | 4 | | 5 | | 6 | | 6 | | 6 | | 6 | | 7 | | 7 | | 7 | +---------+ 25 rows in set (0.00 sec)
4.4列出classid最大的值
mysql> select Age,ClassID from students order by ClassID desc limit 1;
mysql> select Age,ClassID from students order by ClassID desc limit 1; +-----+---------+ | Age | ClassID | +-----+---------+ | 17 | 7 | +-----+---------+ 1 row in set (0.00 sec)
5.过滤数据 where
where 5.1列出classid的值为7的行 mysql> select Name,ClassID from students where ClassID = 7;
mysql> select Name,ClassID from students where ClassID = 7; +-----------+---------+ | Name | ClassID | +-----------+---------+ | Lin Daiyu | 7 | | Hua Rong | 7 | | Diao Chan | 7 | +-----------+---------+ 3 rows in set (0.00 sec)
where子句操作符
= 等于 <> 不等于 < 小于 >= 大于等于 between 在指定两个值之间 5.2检查单个值 mysql> select Name,ClassID from students where Name = 'lin daiyu'; 不区分大小写
mysql> select Name,ClassID from students where Name = 'lin daiyu'; +-----------+---------+ | Name | ClassID | +-----------+---------+ | Lin Daiyu | 7 | +-----------+---------+ 1 row in set (0.00 sec)
mysql> select Name,ClassID from students where ClassID >= 3;
mysql> select Name,ClassID from students where ClassID >= 3; +---------------+---------+ | Name | ClassID | +---------------+---------+ | Ding Dian | 4 | | Yu Yutong | 3 | | Shi Qing | 5 | | Xi Ren | 3 | | Lin Daiyu | 7 | | Ren Yingying | 6 | | Yue Lingshan | 3 | | Yuan Chengzhi | 6 | | Lu Wushuang | 3 | | Duan Yu | 4 | | Lin Chong | 4 | | Hua Rong | 7 | | Xue Baochai | 6 | | Diao Chan | 7 | | Huang Yueying | 6 | | Ma Chao | 4 | +---------------+---------+ 16 rows in set (0.00 sec)
5.3不匹配检查 mysql> select Name,ClassID from students where ClassID <> 7;
mysql> select Name,ClassID from students where ClassID <> 7; +---------------+---------+ | Name | ClassID | +---------------+---------+ | Shi Zhongyu | 2 | | Shi Potian | 1 | | Xie Yanke | 2 | | Ding Dian | 4 | | Yu Yutong | 3 | | Shi Qing | 5 | | Xi Ren | 3 | | Ren Yingying | 6 | | Yue Lingshan | 3 | | Yuan Chengzhi | 6 | | Wen Qingqing | 1 | | Tian Boguang | 2 | | Lu Wushuang | 3 | | Duan Yu | 4 | | Xu Zhu | 1 | | Lin Chong | 4 | | Xue Baochai | 6 | | Huang Yueying | 6 | | Xiao Qiao | 1 | | Ma Chao | 4 | +---------------+---------+ 20 rows in set (0.00 sec)
5.4范围检查
mysql> select Name,ClassID from students where ClassID between 3 and 7;
mysql> select Name,ClassID from students where ClassID between 3 and 7; +---------------+---------+ | Name | ClassID | +---------------+---------+ | Ding Dian | 4 | | Yu Yutong | 3 | | Shi Qing | 5 | | Xi Ren | 3 | | Lin Daiyu | 7 | | Ren Yingying | 6 | | Yue Lingshan | 3 | | Yuan Chengzhi | 6 | | Lu Wushuang | 3 | | Duan Yu | 4 | | Lin Chong | 4 | | Hua Rong | 7 | | Xue Baochai | 6 | | Diao Chan | 7 | | Huang Yueying | 6 | | Ma Chao | 4 | +---------------+---------+ 16 rows in set (0.00 sec)
5.5空值检查 mysql> select ClassID from students where ClassID is null;
mysql> select ClassID from students where ClassID is null; +---------+ | ClassID | +---------+ | NULL | | NULL | +---------+ 2 rows in set (0.01 sec)
6.数据过滤 组合where子句
6.1and操作符 显示满足所有条件的行 mysql> select ClassID,Age from students where ClassID = 7 and Age >= 19;
mysql> select ClassID,Age from students where ClassID = 7 and Age >= 19; +---------+-----+ | ClassID | Age | +---------+-----+ | 7 | 23 | | 7 | 19 | +---------+-----+ 2 rows in set (0.01 sec)
6.2or操作符 显示任一条件的行
mysql> select ClassID,Age from students where ClassID = 7 or Age >= 25;
mysql> select ClassID,Age from students where ClassID = 7 or Age >= 25; +---------+-----+ | ClassID | Age | +---------+-----+ | 2 | 53 | | 4 | 32 | | 3 | 26 | | 5 | 46 | | 7 | 17 | | 2 | 33 | | 4 | 25 | | 7 | 23 | | 7 | 19 | | NULL | 27 | | NULL | 100 | +---------+-----+ 11 rows in set (0.00 sec)
6.3计算次序 mysql> select ClassID,Age from students where ClassID = 7 or Age >= 25 and Gender = 'F';
mysql> select ClassID,Age from students where ClassID = 7 or Age >= 25 and Gender = 'F'; +---------+-----+ | ClassID | Age | +---------+-----+ | 7 | 17 | | 7 | 23 | | 7 | 19 | +---------+-----+ 3 rows in set (0.00 sec)
mysql> select ClassID,Age from students where (ClassID = 7 or Age >= 25) and Gender = 'F';
mysql> select ClassID,Age from students where (ClassID = 7 or Age >= 25) and Gender = 'F'; +---------+-----+ | ClassID | Age | +---------+-----+ | 7 | 17 | | 7 | 19 | +---------+-----+ 2 rows in set (0.00 sec)
6.4in操作符 指定条件范围 mysql> select ClassID,Age from students where ClassID in (1,7) order by Age;
mysql> select ClassID,Age from students where ClassID in (1,7) order by Age; +---------+-----+ | ClassID | Age | +---------+-----+ | 7 | 17 | | 1 | 19 | | 7 | 19 | | 1 | 20 | | 1 | 21 | | 1 | 22 | | 7 | 23 | +---------+-----+ 7 rows in set (0.00 sec)
mysql> select ClassID,Age from students where ClassID in (1,7) and Gender = 'F';
mysql> select ClassID,Age from students where ClassID in (1,7) and Gender = 'F'; +---------+-----+ | ClassID | Age | +---------+-----+ | 7 | 17 | | 1 | 19 | | 7 | 19 | | 1 | 20 | +---------+-----+ 4 rows in set (0.00 sec)
mysql> select ClassID,Age from students where ClassID = 1 OR ClassID = 7 order by Age;
mysql> select ClassID,Age from students where ClassID = 1 OR ClassID = 7 order by Age; +---------+-----+ | ClassID | Age | +---------+-----+ | 7 | 17 | | 1 | 19 | | 7 | 19 | | 1 | 20 | | 1 | 21 | | 1 | 22 | | 7 | 23 | +---------+-----+ 7 rows in set (0.00 sec)
6.5NOT操作符 否定后跟条件 mysql> select ClassID,Age from students where ClassID not in (1,7) order by Age;
mysql> select ClassID,Age from students where ClassID not in (1,7) order by Age; +---------+-----+ | ClassID | Age | +---------+-----+ | 3 | 17 | | 6 | 18 | | 3 | 19 | | 3 | 19 | | 4 | 19 | | 6 | 20 | | 2 | 22 | | 6 | 22 | | 6 | 23 | | 4 | 23 | | 4 | 25 | | 3 | 26 | | 4 | 32 | | 2 | 33 | | 5 | 46 | | 2 | 53 | +---------+-----+ 16 rows in set (0.01 sec)
7.用通配符进行过滤 like操作符
like操作符 区分大小写
7.1%通配符 %表示任意 匹配多个字符 以S开头 mysql> select ClassID,Age,Name from students where Name like 'S%';
mysql> select ClassID,Age,Name from students where Name like 'S%'; +---------+-----+-------------+ | ClassID | Age | Name | +---------+-----+-------------+ | 2 | 22 | Shi Zhongyu | | 1 | 22 | Shi Potian | | 5 | 46 | Shi Qing | | NULL | 100 | Sun Dasheng | +---------+-----+-------------+ 4 rows in set (0.00 sec)
包含ong
mysql> select ClassID,Age,Name from students where Name like '%ong%';
mysql> select ClassID,Age,Name from students where Name like '%ong%'; +---------+-----+-------------+ | ClassID | Age | Name | +---------+-----+-------------+ | 2 | 22 | Shi Zhongyu | | 3 | 26 | Yu Yutong | | 4 | 25 | Lin Chong | | 7 | 23 | Hua Rong | +---------+-----+-------------+ 4 rows in set (0.00 sec)
mysql> select ClassID,Age,Name from students where Name like 'S%g';
mysql> select ClassID,Age,Name from students where Name like 'S%g'; +---------+-----+-------------+ | ClassID | Age | Name | +---------+-----+-------------+ | 5 | 46 | Shi Qing | | NULL | 100 | Sun Dasheng | +---------+-----+-------------+ 2 rows in set (0.00 sec)
7.2_下划线通配符 只匹配单个字符 需要1 anc zzz 查找时为like '_ anc zzz' 格式
8.用正则表达式搜索
不区分大小写 8.1基本字符匹配 regexp 后的作为正则表达式 mysql> select Age from students where Age regexp '100' order by Age;
mysql> select Age from students where Age regexp '100' order by Age; +-----+ | Age | +-----+ | 100 | +-----+ 1 row in set (0.00 sec)
mysql> select Age from students where Age regexp '.2' order by Age;
mysql> select Age from students where Age regexp '.2' order by Age; +-----+ | Age | +-----+ | 22 | | 22 | | 22 | | 32 | +-----+ 4 rows in set (0.00 sec)
. 匹配任意一个字符
8.2 进行or匹配 使用 | mysql> select Age from students where Age regexp '19|22' order by Age;
mysql> select Age from students where Age regexp '19|22' order by Age; +-----+ | Age | +-----+ | 19 | | 19 | | 19 | | 19 | | 19 | | 22 | | 22 | | 22 | +-----+ 8 rows in set (0.00 sec)
8.3匹配几个字符之一 mysql> select Age from students where Age regexp '[123]' order by Age;
mysql> select Age from students where Age regexp '[123]' order by Age; +-----+ | Age | +-----+ | 17 | | 17 | | 18 | | 19 | | 19 | | 19 | | 19 | | 19 | | 20 | | 20 | | 21 | | 22 | | 22 | | 22 | | 23 | | 23 | | 23 | | 25 | | 26 | | 27 | | 32 | | 33 | | 53 | | 100 | +-----+ 24 rows in set (0.00 sec)
[123]定义一组字符，匹配1或2或3
8.4匹配范围
9.创建计算字段
9.2拼接字段 Concat () 拼接串，把多个串连接成一个较长的串，需要四个元素 存储在Name列的名字 包含一个空格和一个左圆括号的串 存储在Age列的数字 包括一个右圆括号的串 mysql> select Concat(Name, ' (', Age, ')') from students order by Name;
mysql> select Concat(Name, ' (', Age, ')') from students order by Name; +------------------------------+ | Concat(Name, ' (', Age, ')') | +------------------------------+ | Diao Chan (19) | | Ding Dian (32) | | Duan Yu (19) | | Hua Rong (23) | | Huang Yueying (22) | | Lin Chong (25) | | Lin Daiyu (17) | | Lu Wushuang (17) | | Ma Chao (23) | | Ren Yingying (20) | | Shi Potian (22) | | Shi Qing (46) | | Shi Zhongyu (22) | | Sun Dasheng (100) | | Tian Boguang (33) | | Wen Qingqing (19) | | Xi Ren (19) | | Xiao Qiao (20) | | Xie Yanke (53) | | Xu Xian (27) | | Xu Zhu (21) | | Xue Baochai (18) | | Yu Yutong (26) | | Yuan Chengzhi (23) | | Yue Lingshan (19) | +------------------------------+ 25 rows in set (0.01 sec)
9.2.1使用别名上面是一个未命名的列，客户机无法引用 函数RTrim()去除列值右边的空格
SQL支持别名 别名（alias）是一个字段或值的替换名。别名用AS关键字赋予
mysql> select Concat(RTrim (Name), ' (', RTrim(Age), ')') AS xkf from students order by Name;
mysql> select Concat(RTrim (Name), ' (', RTrim(Age), ')') AS xkf from students order by Name; +--------------------+ | xkf | +--------------------+ | Diao Chan (19) | | Ding Dian (32) | | Duan Yu (19) | | Hua Rong (23) | | Huang Yueying (22) | | Lin Chong (25) | | Lin Daiyu (17) | | Lu Wushuang (17) | | Ma Chao (23) | | Ren Yingying (20) | | Shi Potian (22) | | Shi Qing (46) | | Shi Zhongyu (22) | | Sun Dasheng (100) | | Tian Boguang (33) | | Wen Qingqing (19) | | Xi Ren (19) | | Xiao Qiao (20) | | Xie Yanke (53) | | Xu Xian (27) | | Xu Zhu (21) | | Xue Baochai (18) | | Yu Yutong (26) | | Yuan Chengzhi (23) | | Yue Lingshan (19) | +--------------------+ 25 rows in set (0.00 sec)
10.使用数据处理函数
10.1文本处理函数
10.0.1使用Upper()函数 ： 将文本转换为大写因此列出两次
mysql> select Name, Upper(Name) AS Name_xkf from students order by Name;
View Code11.使用子查询
11.1列出classid=2的stuid列的内容
mysql> select StuID from students where ClassID = '2';
mysql> select StuID from students where ClassID = '2'; +-------+ | StuID | +-------+ | 1 | | 3 | | 13 | +-------+ 3 rows in set (0.00 sec)
11.2列出stuid=1和3,13的Age 使用in子句
select Age from students where StuID in (1,3,13);
mysql> select Age from students where StuID in (1,3,13); +-----+ | Age | +-----+ | 22 | | 53 | | 33 | +-----+ 3 rows in set (0.00 sec)
11.3将两个查询语句合成一条
mysql> select Age from students where StuID in (select StuID from students where ClassID = '2');
mysql> select Age from students where StuID in (select StuID from students where ClassID = '2'); +-----+ | Age | +-----+ | 22 | | 53 | | 33 | +-----+ 3 rows in set (0.00 sec)
12.联结表
12.1创建联结 规则：首先列出所有表，然后定义表之间的关系
列出classes,students表中 满足classes.ClassID = students.StuID 条件的Class,StuID,Name列，
mysql> select Class,StuID,Name from classes,students where classes.ClassID = students.StuID order by Class,StuID;
mysql> select Class,StuID,Name from classes,students where classes.ClassID = students.StuID order by Class; +----------------+-------+-------------+ | Class | StuID | Name | +----------------+-------+-------------+ | Emei Pai | 2 | Shi Potian | | Lianshan Pai | 6 | Shi Qing | | Ming Jiao | 7 | Xi Ren | | QingCheng Pai | 3 | Xie Yanke | | Riyue Shenjiao | 5 | Yu Yutong | | Shaolin Pai | 1 | Shi Zhongyu | | Wudang Pai | 4 | Ding Dian | | Xiaoyao Pai | 8 | Lin Daiyu | +----------------+-------+-------------+ 8 rows in set (0.00 sec)
能达到一样效果
mysql> select Class,StuID,Name from classes,students where classes.ClassID = students.StuID;
mysql> select Class,StuID,Name from classes,students where classes.ClassID = students.StuID; +----------------+-------+-------------+ | Class | StuID | Name | +----------------+-------+-------------+ | Shaolin Pai | 1 | Shi Zhongyu | | Emei Pai | 2 | Shi Potian | | QingCheng Pai | 3 | Xie Yanke | | Wudang Pai | 4 | Ding Dian | | Riyue Shenjiao | 5 | Yu Yutong | | Lianshan Pai | 6 | Shi Qing | | Ming Jiao | 7 | Xi Ren | | Xiaoyao Pai | 8 | Lin Daiyu | +----------------+-------+-------------+ 8 rows in set (0.00 sec)
12.1.1笛卡儿积 第一个表的行数乘以第二个表的行数 返回的数据包括了所有数据，以及不正确和不存在的；
mysql> select Class,StuID,Name from classes,students order by Class,StuID;
mysql> select Class,StuID,Name from classes,students order by Class,StuID; +----------------+-------+---------------+ | Class | StuID | Name | +----------------+-------+---------------+ | Emei Pai | 1 | Shi Zhongyu | | Emei Pai | 2 | Shi Potian | | Emei Pai | 3 | Xie Yanke | | Emei Pai | 4 | Ding Dian | | Emei Pai | 5 | Yu Yutong | | Emei Pai | 6 | Shi Qing | | Emei Pai | 7 | Xi Ren | | Emei Pai | 8 | Lin Daiyu | | Emei Pai | 9 | Ren Yingying | | Emei Pai | 10 | Yue Lingshan | | Emei Pai | 11 | Yuan Chengzhi | | Emei Pai | 12 | Wen Qingqing | | Emei Pai | 13 | Tian Boguang | | Emei Pai | 14 | Lu Wushuang | | Emei Pai | 15 | Duan Yu | | Emei Pai | 16 | Xu Zhu | | Emei Pai | 17 | Lin Chong | | Emei Pai | 18 | Hua Rong | | Emei Pai | 19 | Xue Baochai | | Emei Pai | 20 | Diao Chan | | Emei Pai | 21 | Huang Yueying | | Emei Pai | 22 | Xiao Qiao | | Emei Pai | 23 | Ma Chao | | Emei Pai | 24 | Xu Xian | | Emei Pai | 25 | Sun Dasheng | | Lianshan Pai | 1 | Shi Zhongyu | | Lianshan Pai | 2 | Shi Potian | | Lianshan Pai | 3 | Xie Yanke | | Lianshan Pai | 4 | Ding Dian | | Lianshan Pai | 5 | Yu Yutong | | Lianshan Pai | 6 | Shi Qing | | Lianshan Pai | 7 | Xi Ren | | Lianshan Pai | 8 | Lin Daiyu | | Lianshan Pai | 9 | Ren Yingying | | Lianshan Pai | 10 | Yue Lingshan | | Lianshan Pai | 11 | Yuan Chengzhi | | Lianshan Pai | 12 | Wen Qingqing | | Lianshan Pai | 13 | Tian Boguang | | Lianshan Pai | 14 | Lu Wushuang | | Lianshan Pai | 15 | Duan Yu | | Lianshan Pai | 16 | Xu Zhu | | Lianshan Pai | 17 | Lin Chong | | Lianshan Pai | 18 | Hua Rong | | Lianshan Pai | 19 | Xue Baochai | | Lianshan Pai | 20 | Diao Chan | | Lianshan Pai | 21 | Huang Yueying | | Lianshan Pai | 22 | Xiao Qiao | | Lianshan Pai | 23 | Ma Chao | | Lianshan Pai | 24 | Xu Xian | | Lianshan Pai | 25 | Sun Dasheng | | Ming Jiao | 1 | Shi Zhongyu | | Ming Jiao | 2 | Shi Potian | | Ming Jiao | 3 | Xie Yanke | | Ming Jiao | 4 | Ding Dian | | Ming Jiao | 5 | Yu Yutong | | Ming Jiao | 6 | Shi Qing | | Ming Jiao | 7 | Xi Ren | | Ming Jiao | 8 | Lin Daiyu | | Ming Jiao | 9 | Ren Yingying | | Ming Jiao | 10 | Yue Lingshan | | Ming Jiao | 11 | Yuan Chengzhi | | Ming Jiao | 12 | Wen Qingqing | | Ming Jiao | 13 | Tian Boguang | | Ming Jiao | 14 | Lu Wushuang | | Ming Jiao | 15 | Duan Yu | | Ming Jiao | 16 | Xu Zhu | | Ming Jiao | 17 | Lin Chong | | Ming Jiao | 18 | Hua Rong | | Ming Jiao | 19 | Xue Baochai | | Ming Jiao | 20 | Diao Chan | | Ming Jiao | 21 | Huang Yueying | | Ming Jiao | 22 | Xiao Qiao | | Ming Jiao | 23 | Ma Chao | | Ming Jiao | 24 | Xu Xian | | Ming Jiao | 25 | Sun Dasheng | | QingCheng Pai | 1 | Shi Zhongyu | | QingCheng Pai | 2 | Shi Potian | | QingCheng Pai | 3 | Xie Yanke | | QingCheng Pai | 4 | Ding Dian | | QingCheng Pai | 5 | Yu Yutong | | QingCheng Pai | 6 | Shi Qing | | QingCheng Pai | 7 | Xi Ren | | QingCheng Pai | 8 | Lin Daiyu | | QingCheng Pai | 9 | Ren Yingying | | QingCheng Pai | 10 | Yue Lingshan | | QingCheng Pai | 11 | Yuan Chengzhi | | QingCheng Pai | 12 | Wen Qingqing | | QingCheng Pai | 13 | Tian Boguang | | QingCheng Pai | 14 | Lu Wushuang | | QingCheng Pai | 15 | Duan Yu | | QingCheng Pai | 16 | Xu Zhu | | QingCheng Pai | 17 | Lin Chong | | QingCheng Pai | 18 | Hua Rong | | QingCheng Pai | 19 | Xue Baochai | | QingCheng Pai | 20 | Diao Chan | | QingCheng Pai | 21 | Huang Yueying | | QingCheng Pai | 22 | Xiao Qiao | | QingCheng Pai | 23 | Ma Chao | | QingCheng Pai | 24 | Xu Xian | | QingCheng Pai | 25 | Sun Dasheng | | Riyue Shenjiao | 1 | Shi Zhongyu | | Riyue Shenjiao | 2 | Shi Potian | | Riyue Shenjiao | 3 | Xie Yanke | | Riyue Shenjiao | 4 | Ding Dian | | Riyue Shenjiao | 5 | Yu Yutong | | Riyue Shenjiao | 6 | Shi Qing | | Riyue Shenjiao | 7 | Xi Ren | | Riyue Shenjiao | 8 | Lin Daiyu | | Riyue Shenjiao | 9 | Ren Yingying | | Riyue Shenjiao | 10 | Yue Lingshan | | Riyue Shenjiao | 11 | Yuan Chengzhi | | Riyue Shenjiao | 12 | Wen Qingqing | | Riyue Shenjiao | 13 | Tian Boguang | | Riyue Shenjiao | 14 | Lu Wushuang | | Riyue Shenjiao | 15 | Duan Yu | | Riyue Shenjiao | 16 | Xu Zhu | | Riyue Shenjiao | 17 | Lin Chong | | Riyue Shenjiao | 18 | Hua Rong | | Riyue Shenjiao | 19 | Xue Baochai | | Riyue Shenjiao | 20 | Diao Chan | | Riyue Shenjiao | 21 | Huang Yueying | | Riyue Shenjiao | 22 | Xiao Qiao | | Riyue Shenjiao | 23 | Ma Chao | | Riyue Shenjiao | 24 | Xu Xian | | Riyue Shenjiao | 25 | Sun Dasheng | | Shaolin Pai | 1 | Shi Zhongyu | | Shaolin Pai | 2 | Shi Potian | | Shaolin Pai | 3 | Xie Yanke | | Shaolin Pai | 4 | Ding Dian | | Shaolin Pai | 5 | Yu Yutong | | Shaolin Pai | 6 | Shi Qing | | Shaolin Pai | 7 | Xi Ren | | Shaolin Pai | 8 | Lin Daiyu | | Shaolin Pai | 9 | Ren Yingying | | Shaolin Pai | 10 | Yue Lingshan | | Shaolin Pai | 11 | Yuan Chengzhi | | Shaolin Pai | 12 | Wen Qingqing | | Shaolin Pai | 13 | Tian Boguang | | Shaolin Pai | 14 | Lu Wushuang | | Shaolin Pai | 15 | Duan Yu | | Shaolin Pai | 16 | Xu Zhu | | Shaolin Pai | 17 | Lin Chong | | Shaolin Pai | 18 | Hua Rong | | Shaolin Pai | 19 | Xue Baochai | | Shaolin Pai | 20 | Diao Chan | | Shaolin Pai | 21 | Huang Yueying | | Shaolin Pai | 22 | Xiao Qiao | | Shaolin Pai | 23 | Ma Chao | | Shaolin Pai | 24 | Xu Xian | | Shaolin Pai | 25 | Sun Dasheng | | Wudang Pai | 1 | Shi Zhongyu | | Wudang Pai | 2 | Shi Potian | | Wudang Pai | 3 | Xie Yanke | | Wudang Pai | 4 | Ding Dian | | Wudang Pai | 5 | Yu Yutong | | Wudang Pai | 6 | Shi Qing | | Wudang Pai | 7 | Xi Ren | | Wudang Pai | 8 | Lin Daiyu | | Wudang Pai | 9 | Ren Yingying | | Wudang Pai | 10 | Yue Lingshan | | Wudang Pai | 11 | Yuan Chengzhi | | Wudang Pai | 12 | Wen Qingqing | | Wudang Pai | 13 | Tian Boguang | | Wudang Pai | 14 | Lu Wushuang | | Wudang Pai | 15 | Duan Yu | | Wudang Pai | 16 | Xu Zhu | | Wudang Pai | 17 | Lin Chong | | Wudang Pai | 18 | Hua Rong | | Wudang Pai | 19 | Xue Baochai | | Wudang Pai | 20 | Diao Chan | | Wudang Pai | 21 | Huang Yueying | | Wudang Pai | 22 | Xiao Qiao | | Wudang Pai | 23 | Ma Chao | | Wudang Pai | 24 | Xu Xian | | Wudang Pai | 25 | Sun Dasheng | | Xiaoyao Pai | 1 | Shi Zhongyu | | Xiaoyao Pai | 2 | Shi Potian | | Xiaoyao Pai | 3 | Xie Yanke | | Xiaoyao Pai | 4 | Ding Dian | | Xiaoyao Pai | 5 | Yu Yutong | | Xiaoyao Pai | 6 | Shi Qing | | Xiaoyao Pai | 7 | Xi Ren | | Xiaoyao Pai | 8 | Lin Daiyu | | Xiaoyao Pai | 9 | Ren Yingying | | Xiaoyao Pai | 10 | Yue Lingshan | | Xiaoyao Pai | 11 | Yuan Chengzhi | | Xiaoyao Pai | 12 | Wen Qingqing | | Xiaoyao Pai | 13 | Tian Boguang | | Xiaoyao Pai | 14 | Lu Wushuang | | Xiaoyao Pai | 15 | Duan Yu | | Xiaoyao Pai | 16 | Xu Zhu | | Xiaoyao Pai | 17 | Lin Chong | | Xiaoyao Pai | 18 | Hua Rong | | Xiaoyao Pai | 19 | Xue Baochai | | Xiaoyao Pai | 20 | Diao Chan | | Xiaoyao Pai | 21 | Huang Yueying | | Xiaoyao Pai | 22 | Xiao Qiao | | Xiaoyao Pai | 23 | Ma Chao | | Xiaoyao Pai | 24 | Xu Xian | | Xiaoyao Pai | 25 | Sun Dasheng | +----------------+-------+---------------+ 200 rows in set (0.00 sec)
12.2内部联结 （等值联结）
from子句不同，以inner join 指定传递给特定 on子句； on子句与where子句相同
mysql> select Class,StuID,Name from classes inner join students on classes.ClassID = students.StuID;
mysql> select Class,StuID,Name from classes inner join students on classes.ClassID = students.StuID; +----------------+-------+-------------+ | Class | StuID | Name | +----------------+-------+-------------+ | Shaolin Pai | 1 | Shi Zhongyu | | Emei Pai | 2 | Shi Potian | | QingCheng Pai | 3 | Xie Yanke | | Wudang Pai | 4 | Ding Dian | | Riyue Shenjiao | 5 | Yu Yutong | | Lianshan Pai | 6 | Shi Qing | | Ming Jiao | 7 | Xi Ren | | Xiaoyao Pai | 8 | Lin Daiyu | +----------------+-------+-------------+ 8 rows in set (0.00 sec)
13.组合查询
13.1创建组合查询 13.1.1使用UNION 在各语句之间使用union连接。将输出组合成一个查询结果集。
mysql> select StuID,Name,Age, Gender from students where StuID >=20;
mysql> select StuID,Name,Age, Gender from students where Age in (22,100);
mysql> select StuID,Name,Age, Gender from students where StuID >=20 UNION select StuID,Name,Age, Gender from students where Age in (22,100);
mysql> select StuID,Name,Age, Gender from students where StuID >=20; +-------+---------------+-----+--------+ | StuID | Name | Age | Gender | +-------+---------------+-----+--------+ | 20 | Diao Chan | 19 | F | | 21 | Huang Yueying | 22 | F | | 22 | Xiao Qiao | 20 | F | | 23 | Ma Chao | 23 | M | | 24 | Xu Xian | 27 | M | | 25 | Sun Dasheng | 100 | M | +-------+---------------+-----+--------+ 6 rows in set (0.01 sec) mysql> select StuID,Name,Age, Gender from students where Age in (22,100); +-------+---------------+-----+--------+ | StuID | Name | Age | Gender | +-------+---------------+-----+--------+ | 1 | Shi Zhongyu | 22 | M | | 2 | Shi Potian | 22 | M | | 21 | Huang Yueying | 22 | F | | 25 | Sun Dasheng | 100 | M | +-------+---------------+-----+--------+ 4 rows in set (0.01 sec) mysql> select StuID,Name,Age, Gender from students where StuID >=20 UNION select StuID,Name,Age, Gender from students where Age in (22,100); +-------+---------------+-----+--------+ | StuID | Name | Age | Gender | +-------+---------------+-----+--------+ | 20 | Diao Chan | 19 | F | | 21 | Huang Yueying | 22 | F | | 22 | Xiao Qiao | 20 | F | | 23 | Ma Chao | 23 | M | | 24 | Xu Xian | 27 | M | | 25 | Sun Dasheng | 100 | M | | 1 | Shi Zhongyu | 22 | M | | 2 | Shi Potian | 22 | M | +-------+---------------+-----+--------+ 8 rows in set (0.00 sec) mysql>
13.1.2对组合查询结果排序 倒序
mysql> select StuID,Name,Age, Gender from students where StuID >=20 UNION select StuID,Name,Age, Gender from students where Age in (22,100) order by StuID desc;
mysql> select StuID,Name,Age, Gender from students where StuID >=20 UNION select StuID,Name,Age, Gender from students where Age in (22,100) order by StuID desc; +-------+---------------+-----+--------+ | StuID | Name | Age | Gender | +-------+---------------+-----+--------+ | 25 | Sun Dasheng | 100 | M | | 24 | Xu Xian | 27 | M | | 23 | Ma Chao | 23 | M | | 22 | Xiao Qiao | 20 | F | | 21 | Huang Yueying | 22 | F | | 20 | Diao Chan | 19 | F | | 2 | Shi Potian | 22 | M | | 1 | Shi Zhongyu | 22 | M | +-------+---------------+-----+--------+ 8 rows in set (0.00 sec)
二.创建，插入，更新和操纵表
14.数据插入
14.1插入完整的行
插入一个新学生到teachers表，每个列必须提供一个值，没有值用null值
mysql> insert into teachers values('5','Xie keng','66','M');
mysql> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec) mysql> insert into teachers values('5','Xie keng','66','M'); Query OK, 1 row affected (0.01 sec) mysql> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | Xie keng | 66 | M | +-----+---------------+-----+--------+ 5 rows in set (0.00 sec)
14.2插入多个行 单条insert有多组值，每组值用一对圆括号括起来，用逗号分隔
mysql> insert into teachers(TID,Name,Age,Gender) values('6','Fie Keng','77','M'),('7','XX Xem','88','F');
mysql> insert into teachers(TID,Name,Age,Gender) values('6','Fie Keng','77','M'),('7','XX Xem','88','F'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | Xie keng | 66 | M | | 6 | Fie Keng | 77 | M | | 7 | XX Xem | 88 | F | +-----+---------------+-----+--------+ 7 rows in set (0.00 sec)
14.3插入检索出的数据
insert select 把students表中的Name,Age,Gender列合并到teachers表中，
合并需要两个表中都存在相同的列
mysql> insert into teachers(Name,Age,Gender) select Name,Age,Gender from students;
mysql> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | Xie keng | 66 | M | | 6 | Fie Keng | 77 | M | | 7 | XX Xem | 88 | F | +-----+---------------+-----+--------+ 7 rows in set (0.00 sec) mysql> insert into teachers(Name,Age,Gender) select Name,Age,Gender from students; Query OK, 25 rows affected (0.00 sec) Records: 25 Duplicates: 0 Warnings: 0 mysql> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | Xie keng | 66 | M | | 6 | Fie Keng | 77 | M | | 7 | XX Xem | 88 | F | | 8 | Shi Zhongyu | 22 | M | | 9 | Shi Potian | 22 | M | | 10 | Xie Yanke | 53 | M | | 11 | Ding Dian | 32 | M | | 12 | Yu Yutong | 26 | M | | 13 | Shi Qing | 46 | M | | 14 | Xi Ren | 19 | F | | 15 | Lin Daiyu | 17 | F | | 16 | Ren Yingying | 20 | F | | 17 | Yue Lingshan | 19 | F | | 18 | Yuan Chengzhi | 23 | M | | 19 | Wen Qingqing | 19 | F | | 20 | Tian Boguang | 33 | M | | 21 | Lu Wushuang | 17 | F | | 22 | Duan Yu | 19 | M | | 23 | Xu Zhu | 21 | M | | 24 | Lin Chong | 25 | M | | 25 | Hua Rong | 23 | M | | 26 | Xue Baochai | 18 | F | | 27 | Diao Chan | 19 | F | | 28 | Huang Yueying | 22 | F | | 29 | Xiao Qiao | 20 | F | | 30 | Ma Chao | 23 | M | | 31 | Xu Xian | 27 | M | | 32 | Sun Dasheng | 100 | M | +-----+---------------+-----+--------+ 32 rows in set (0.00 sec)
15.更新数据
update语句更新，由三部分组成，要更新的表，列名和他的新值，确定要更新行的过滤条件
update语句以where子句结束，set命令用来将新值赋予给更新的列
15.1更新单个列
把ID=666的行的 ClassID列的数据修改为99
mysql> update coc set ClassID = '99' where ID = 666；
mysql> select * from coc; +-----+---------+----------+ | ID | ClassID | CourseID | +-----+---------+----------+ | 2 | 1 | 5 | | 3 | 2 | 2 | | 4 | 2 | 6 | | 5 | 3 | 1 | | 6 | 3 | 7 | | 7 | 4 | 5 | | 8 | 4 | 2 | | 9 | 5 | 1 | | 10 | 5 | 9 | | 11 | 6 | 3 | | 12 | 6 | 4 | | 13 | 7 | 4 | | 14 | 7 | 3 | | 33 | 1 | 2 | | 666 | 6 | 66 | +-----+---------+----------+ 15 rows in set (0.00 sec) mysql> update coc set ClassID = '99' where ID = 666; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from coc; +-----+---------+----------+ | ID | ClassID | CourseID | +-----+---------+----------+ | 2 | 1 | 5 | | 3 | 2 | 2 | | 4 | 2 | 6 | | 5 | 3 | 1 | | 6 | 3 | 7 | | 7 | 4 | 5 | | 8 | 4 | 2 | | 9 | 5 | 1 | | 10 | 5 | 9 | | 11 | 6 | 3 | | 12 | 6 | 4 | | 13 | 7 | 4 | | 14 | 7 | 3 | | 33 | 1 | 2 | | 666 | 99 | 66 | +-----+---------+----------+ 15 rows in set (0.00 sec)
15.2更新多个列 只需要单个set命令，每个列=值 用逗号分隔
把ID=666的行的 ClassID列和CourseID的数据修改为55
mysql> update coc set ClassID = '55',CourseID = '55' where ID = 666;
mysql> update coc set ClassID = '55',CourseID = '55' where ID = 666; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from coc; +-----+---------+----------+ | ID | ClassID | CourseID | +-----+---------+----------+ | 2 | 1 | 5 | | 3 | 2 | 2 | | 4 | 2 | 6 | | 5 | 3 | 1 | | 6 | 3 | 7 | | 7 | 4 | 5 | | 8 | 4 | 2 | | 9 | 5 | 1 | | 10 | 5 | 9 | | 11 | 6 | 3 | | 12 | 6 | 4 | | 13 | 7 | 4 | | 14 | 7 | 3 | | 33 | 1 | 2 | | 666 | 55 | 55 | +-----+---------+----------+ 15 rows in set (0.00 sec)
15.3 为了删除某个列的值可将他设置为null(表定义允许NULL值)
把students表中StuID=1列的值更改为null
mysql> update students set TeacherID = NULL where StuID = 1;
mysql> update students set TeacherID = NULL where StuID = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | NULL |
16.删除数据
使用delete语句，删除特定的行或者全部的行；
删除students表中id=1的行
mysql> delete from students where StuID = 1;
Query OK, 1 row affected (0.00 sec)
删除指定的列使用update语句
17.创建表，操纵表
CREATE TABLE 表名 (
属性名 数据类型 [完整约束条件],
属性名 数据类型 [完整约束条件],
...
...
属性名 数据类型 [完整约束条件]
)；
17.1创建表
mysql> create table xkf (
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> create table xkf ( -> id int, -> name varchar(20) -> ); Query OK, 0 rows affected (0.04 sec) mysql> select * from xkf; Empty set (0.01 sec) mysql> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | | user | | xkf | +-------------------+ 9 rows in set (0.00 sec)
17.2查看表结构
mysql> describe classes;
mysql> describe classes; +----------+-------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------------+------+-----+---------+----------------+ | ClassID | tinyint unsigned | NO | PRI | NULL | auto_increment | | Class | varchar(100) | YES | | NULL | | | NumOfStu | smallint unsigned | YES | | NULL | | +----------+-------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
17.3更新表
alter table
给coc表增加xxx列
mysql> alter table coc add xxx CHAR(20);
mysql> alter table coc add xxx CHAR(20); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from coc; +-----+---------+----------+------+ | ID | ClassID | CourseID | xxx | +-----+---------+----------+------+ | 2 | 1 | 5 | NULL | | 3 | 2 | 2 | NULL | | 4 | 2 | 6 | NULL | | 5 | 3 | 1 | NULL | | 6 | 3 | 7 | NULL | | 7 | 4 | 5 | NULL | | 8 | 4 | 2 | NULL | | 9 | 5 | 1 | NULL | | 10 | 5 | 9 | NULL | | 11 | 6 | 3 | NULL | | 12 | 6 | 4 | NULL | | 13 | 7 | 4 | NULL | | 14 | 7 | 3 | NULL | | 33 | 1 | 2 | NULL | | 666 | 55 | 55 | NULL | +-----+---------+----------+------+ 15 rows in set (0.00 sec)
删除刚刚添加的列
mysql> alter table coc drop column xxx;
mysql> alter table coc drop column xxx; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from coc; +-----+---------+----------+ | ID | ClassID | CourseID | +-----+---------+----------+ | 2 | 1 | 5 | | 3 | 2 | 2 | | 4 | 2 | 6 | | 5 | 3 | 1 | | 6 | 3 | 7 | | 7 | 4 | 5 | | 8 | 4 | 2 | | 9 | 5 | 1 | | 10 | 5 | 9 | | 11 | 6 | 3 | | 12 | 6 | 4 | | 13 | 7 | 4 | | 14 | 7 | 3 | | 33 | 1 | 2 | | 666 | 55 | 55 | +-----+---------+----------+ 15 rows in set (0.00 sec)
17.4删除表
drop table 表名
17.5重命名表
格式：ALTER TABLE 旧表名 RENAME 新表名;
mysql> ALTER TABLE xkf RENAME XXX;
Query OK, 0 rows affected (0.01 sec)
18.使用触发器
某条语句在事件执行时自动执行，当某个表发生改变时自动处理就是触发器；触发器是MySQL响应 delete insert update 这三个语句而自动执行的一条MySQL语句（或位于begin和end语句之间的一组语句） 创建触发器，需要给出： 1.唯一的触发器名； 2.触发器关联的表； 3.触发器应该响应的活动（delete语句） 4.触发器何时执行（处理之前或之后） 创建只有一个执行语句的触发器的基本形式如下： create trigger 触发器名 before | after 触发事件 on 表名 for each row 执行语句 创建有多个执行语句的触发器的基本形式如下： create trigger 触发器名 before | after 触发事件 on 表名 for each row begin 执行语句列表 end 查看触发器 show triggers; 在MySQL中，所有触发器的定义都存在information_schema数据库下的triggers表中。查询triggers表，可以查看到数据库中所有触发器的详细信息。查询的语句如下： select * from information_schema.triggers; 指定 select * from information_schema.triggers where trigger_name='触发器名'; 删除触发器 drop trigger 触发器名;
三.安全管理
19.安全管理
19.1管理用户 mysql> use mysql mysql> select user from user;
mysql> select user from user; +------------------+ | user | +------------------+ | jack | | tom | | blog | | mysql.infoschema | | mysql.session | | mysql.sys | | root | +------------------+ 7 rows in set (0.01 sec)
19.2创建用户账号
mysql> create user abc identified by 'p@$$wOrd';
Query OK, 0 rows affected (0.01 sec)
mysql> select user from user;
+------------------+
| user |
+------------------+
| abc |
identified by 'p@$$wOrd'指定扩散口令为纯文本，进行加密
19.3重命名用户
mysql> rename user abc to xxx;
Query OK, 0 rows affected (0.01 sec)
19.4删除用户账号
mysql> drop user xxx;
Query OK, 0 rows affected (0.01 sec)
19.5设置访问权限
19.5.1查看用户权限
mysql> show grants for abc;
+---------------------------------+
| Grants for abc@% |
+---------------------------------+
| GRANT USAGE ON *.* TO `abc`@`%` |
+---------------------------------+
19.5.2赋予用户权限
赋予abc用户使用select命令查看hellodb库中的s'you'bi
mysql> grant select on hellodb.* to abc;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for abc;
+------------------------------------------+
| Grants for abc@% |
+------------------------------------------+
| GRANT USAGE ON *.* TO `abc`@`%` |
| GRANT SELECT ON `hellodb`.* TO `abc`@`%` |
+------------------------------------------+
赋予全部权限
mysql> grant all on hellodb.* to abc;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for abc;
+--------------------------------------------------+
| Grants for abc@% |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `abc`@`%` |
| GRANT ALL PRIVILEGES ON `hellodb`.* TO `abc`@`%` |
+--------------------------------------------------+
2 rows in set (0.00 sec)
19.5.3撤销权限 revoke 撤销的权限必须存在
mysql> revoke select on hellodb.* from abc;
Query OK, 0 rows affected (0.01 sec)
mysql> revoke all on hellodb.* from abc;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for abc;
+---------------------------------+
| Grants for abc@% |
+---------------------------------+
| GRANT USAGE ON *.* TO `abc`@`%` |
+---------------------------------+
1 row in set (0.00 sec)
19.5.4 更改口令密码
mysql> alter user c identified by 'test';
Query OK, 0 rows affected (0.00 sec)
刷新生效
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
20.数据库维护
analyze table check table 发现和修复问题
mysql> analyze table students; +----------------+---------+----------+--------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+--------------------------------------+ | mysql.students | analyze | Error | Table 'mysql.students' doesn't exist | | mysql.students | analyze | status | Operation failed | +----------------+---------+----------+--------------------------------------+ 2 rows in set (0.00 sec) mysql> check table students,teachers; +----------------+-------+----------+--------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+-------+----------+--------------------------------------+ | mysql.students | check | Error | Table 'mysql.students' doesn't exist | | mysql.students | check | status | Operation failed | | mysql.teachers | check | Error | Table 'mysql.teachers' doesn't exist | | mysql.teachers | check | status | Operation failed | +----------------+-------+----------+--------------------------------------+ 4 rows in set (0.00 sec)
