目录
数据库
表
记录
完整性约束
单表查询
多表查询
用户
外键
典型sql语句
建表语句
数据库
对象 | 功能 | 命令 | 详述 | 示例 | 注意 |
数据库 | 查看所有数据库 | SHOW DATABASES; | 可以在数据库内,外执行 | ||
切换数据库 | USE mydb1 | 切换到mydb1数据库,使用该数据库前必须执行该语句 | |||
查看当前所在数据库 | select database(); | ||||
创建 | CREATE DATABASE [IF NOT EXISTS] mydb1; | 如果这个数据已经存在,那么会报错。例如CREATE DATABASE IF NOT EXISTS mydb1,在名为mydb1的数据库不存在时创建该库,这样可以避免报错。 | 例如:CREATE DATABASE mydb1,创建一个名为mydb1的数据库 | ||
删除 | DROP DATABASE [IF EXISTS] mydb1; | 如果这个数据库不存在,那么会报错。DROP DATABASE IF EXISTS mydb1,就算mydb1不存在,也不会的报错 | 例如:DROP DATABASE mydb1,删除名为mydb1的数据库 | ||
修改 | ALTER DATABASE mydb1 CHARACTER SET utf8 | ALTER DATABASE mydb1 CHARACTER SET utf8,修改数据库mydb1的编码为utf8。 | 在MySQL中所有的UTF-8编码都不能使用中间的“-”,即UTF-8要书写为UTF8。 | ||
备份 | mysqldump -u root -p 要备份的数据库的名称 > 要把数据备份到路径(备份的文件的后缀名.sql) | 1.备份后的文件是.sql文件 2.sql文件中只有该数据库中的建表语句和Insert语句包含有数据,没有创建该数据库的语句 3.备份时不需要登陆数据库 | 1.打开cmd窗口,但是不要进行登录 2.输入一个命令:mysqldump -u root -p day16 > i:\day16.sql(不要写分号) | ||
还原(两种方式) | mysql -u root -p 要还原的数据库的名称 < 备份的文件的路径 | 1.该操作在cmd下执行 2.先登陆数据库,创建一个数据库。 3.在新的cmd窗口,不登录,然后使用命令将备份的数据文件导入到新建的数据库中 | 1.登陆数据库,然后执行:create database day16,然后退出数据库,关闭cmd 2.新打开cmd,不登录,输入指令: mysql -u root -p day16 < c:\day16.sql(后面不要写分号) | 1.不可登陆 2.因为备份的文件里面没有创建数据库的语句,创建一个数据库 ,数据库名称可以跟原来不同 | |
source 备份文件的路径 | 1.该操作在cmd下执行 2.创建一个数据库,登陆该数据库,在该数据库下执行命令,将备份文件导入数据库中 | 1.登陆数据库,然后执行:create database day16 2.切换进程该数据库下:use day16 3.在新库下,输入指令: mysql -u root -p day16 < c:\day16.sql(后面不要写分号) | 1.需要登陆,在没有表数据库下导入数据 |
表
对象 | 功能 | 命令 | 详述 | 示例 |
表 | 创建 | CREATE TABLE 表名( 列名 列类型 [约束], 列名 列类型 [约束], ...... ); 约束:primary key/unique/not null | int:整型 double:浮点型,例如double(5,2)表示最多5位,其中必须有2位小数,即最大值为999.99; decimal:泛型型,在表单钱方面使用该类型,因为不会出现精度缺失问题; char:固定长度字符串类型; varchar:可变长度字符串类型; text:字符串类型; blob:字节类型; date:日期类型,格式为:yyyy-MM-dd; time:时间类型,格式为:hh:mm:ss datatime:可表示时间日期,必须手动输入 timestamp:时间戳类型,数据库可自动生成 | CREATE TABLE emp( eid CHAR(6), ename VARCHAR(50), age INT, gender VARCHAR(6), birthday DATE, hiredate DATE, salary DECIMAL(7,2), resume VARCHAR(1000) ); |
l 查看表创建语句 | SHOW CREATE TABLE 表名; | |||
l 查看所有表名称 | SHOW TABLES; | 查看当前数据库中所有表名称 | ||
l 查看表结构 | DESC 表名; | DESC emp,查看emp表结构; | ||
l 删除表 | DROP TABLE 表名; | DROP TABLE emp,删除emp表; | ||
添加列 | ALTER TABLE 表名 ADD 新列名 类型长度; | 给stu表添加classname列: ALTER TABLE stu ADD (classname varchar(100)); | ||
修改列类型 | ALTER TABLE 表名 MODIFY 列名 新类型; | 修改stu表的gender列类型为CHAR(2): ALTER TABLE stu MODIFY gender CHAR(2); | ||
修改列名 | ALTER TABLE 表名 change 旧列名 新列名 新列类型; | 修改stu表的gender列名为sex: ALTER TABLE stu change gender sex CHAR(2); | ||
删除列 | ALTER TABLE 表名 DROP 列名; | 删除stu表的classname列: ALTER TABLE stu DROP classname; | ||
修改表名称 | ALTER TABLE 旧表名 RENAME TO 新表名; | 修改stu表名称为student: ALTER TABLE stu RENAME TO student; |
记录
对象 | 功能 | 命令 | 示例 | 说明 | 注意 |
记录 | 插入数据 | INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2) | INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male'); INSERT INTO stu(sid, sname) VALUES('s_1001', 'zhangSan'); | 1.因为没有插入age和gender列的数据,所以该条记录的age和gender值上为NULL | 注意:所有字符串数据必须使用单引用! |
INSERT INTO 表名 VALUES(值1,值2,…) | INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female'); | 因为没有指定要插入的列,表示按创建表时列的顺序插入所有列的值 | |||
修改数据 | UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件] | UPDATE stu SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’; | |||
UPDATE stu SET sname=’liSi’, age=’20’ WHERE age > 50 AND gender=’male’; | |||||
UPDATE stu SET sname=’wangWu’, age=’30’ WHERE age>60 OR gender=’female’; | |||||
UPDATE stu SET gender=’female’ WHERE gender IS NULL | |||||
UPDATE stu SET age=age+1 WHERE sname=’zhaoLiu’; | |||||
删除数据 | DELETE FROM 表名 [WHERE 条件] | DELETE FROM stu WHERE sid=’s_1001’ | |||
DELETE FROM stu WHERE sname=’chenQi’ OR age > 30; | |||||
DELETE FROM stu; | 删除表所有记录,原理不同:Delete是一条一条删除,Truncate是先把表删除了,在创建一个表结构相同的表,效率比Delete的高,但不可以回滚,Delete可以回滚。 | ||||
TRUNCATE TABLE 表名 | TRUNCATE TABLE stu; |
完整性约束
名称 | 功能 | 详述 | 格式 | 示例 | 注意 |
主键约束 | 用来唯一标识一条记录 | 1.主键不可重复、为空(NULL) 2.使用 PRIMARY KEY关键字指定主键 | 1.创建表:定义列时指定主键: create table tablename( 字段名 类型 primary key, 字段名 类型 ) | create table stu( sid CHAR(6) PRIMARY KEY, sname VARCHAR(20), age INT, gender VARCHAR(10) ); | |
2.创建表:定义列之后独立指定主键: create table tablename( 字段名1 类型1, 字段名2 类型2, primary key(字段名1) ) | CREATE TABLE stu( sid CHAR(6), sname VARCHAR(20), age INT, gender VARCHAR(10), PRIMARY KEY(sid) ); | ||||
3.修改表时指定主键: | ALTER TABLE stu ADD PRIMARY KEY(sid); | ||||
4.删除主键: | ALTER TABLE stu DROP PRIMARY KEY; | 只是删除主键约束,而不会删除主键列 | |||
3.如果是int类型,可设置为自动增长 | 5.1 创建表时设置主键自增长 create table tablename( 字段名 类型 primary key auto_increment, 字段名 类型 ) | CREATE TABLE stu( sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(20), age INT, gender VARCHAR(10) ); | 1.当主键设置为自动增长后,在没有给出主键值时,主键的值会自动生成,而且是最大主键值+1,也就不会出现重复主键的可能 2.主键必须是整型才可以自增长 | ||
5.2 修改表时设置主键自增长 ALTER TABLE 表名 CHANGE 字段名 类型 AUTO_INCREMENT; | ALTER TABLE stu CHANGE sid INT AUTO_INCREMENT; | ||||
5.3 修改表时删除主键自增长 ALTER TABLE 表名 CHANGE 字段名 类型; | ALTER TABLE stu CHANGE sid sid INT; | ||||
非空 | 不允许列值为空 | 1.使用NOT NULL表示 2.插入记录时,对添加了非空约束的列一定要给值; 3..在修改记录时,不能把非空列的值设置为NULL | 1.创建表时指定非空约束 create table tablename( 字段名 类型 primary key auto_increment, 字段名 类型 not null ) | CREATE TABLE stu( sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(10) NOT NULL, age INT, gender VARCHAR(10) ); | 添加非空约束后必须设置值,否则报错: Column '列名' cannot be null |
2.创建表后添加非空约束 ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL; | ALTER TABLE mydb MODIFY dept VARCHAR(10) NOT NULL; | ||||
2.修改删除非空约束 ALTER TABLE 表名 MODIFY 列名 类型; | ALTER TABLE mydb MODIFY dept VARCHAR(10); | 类型可以跟原来不同,但不要有Not NULL了,就表示删除了 | |||
唯一 | 不允许列值重复 | 1.字段的值必须是唯一,于主键类似 2.使用 unique 关键字 | 1.创建表时指定唯一约束 create table tablename( 字段名 类型 primary key auto_increment, 字段名 类型 unique ) | CREATE TABLE tab_ab( sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(10) UNIQUE ); | |
2.创建表后添加唯一约束 ALTER TABLE 表名 MODIFY 列名 类型 unique; | ALTER TABLE mydb MODIFY dept VARCHAR(10) unique; | ||||
2.修改删除唯一约束 ALTER TABLE 表名 MODIFY 列名 类型; | ALTER TABLE mydb MODIFY dept VARCHAR(10); |
单表查询
对象 | 分类 | 功能 | 命令 | 示例 | 说明 | 注意 |
单表 | 基础查询 | 查询所有列 | SELECT * FROM 表名; | SELECT * FROM stu; | ||
查询指定列 | SELECT 列名1, 列名2 FROM 表名; | SELECT sid, sname, age FROM stu; | ||||
条件查询 | 条件查询就是在查询时给出WHERE子句 | 1.查询性别为女,并且年龄小于50的记录 SELECT * FROM stu WHERE gender='female' AND ge<50; 2.查询学号为S_1001,或者姓名为liSi的记录 SELECT * FROM stu WHERE sid="S_1001" OR sname = "liSi"; 3.查询学号为S_1001,S_1002,S_1003的记录 SELECT * FROM stu WHERE sid = "S_1001" OR sid="S_1002" OR sid="S_1003"; SELECT * FROM stu WHERE sid IN ("S_1001","S_1002","S_1003"); 4.查询学号不是S_1001,S_1002,S_1003的记录 SELECT * FROM stu WHERE sid <> "S_1001" AND sid <> "S_1002" AND sid <> "S_1003"; SELECT * FROM stu WHERE sid NOT IN ("S_1001","S_1002","S_1003"); 5.查询年龄为null的记录 SELECT * FROM stu WHERE age IS NULL; 6.查询年龄在20到40之间的学生记录 SELECT * FROM stu WHERE age >= 20 AND age <= 40; SELECT * FROM stu WHERE age BETWEEN 20 AND 40; 7.查询性别非男的学生记录 SELECT * FROM stu WHERE gender <> "male"; SELECT * FROM stu WHERE gender != "male"; SELECT * FROM stu WHERE NOT gender = "male"; 8.查询姓名不为null的学生记录 SELECT * FROM stu WHERE sname IS NULL; SELECT * FROM stu WHERE sname IS NOT NULL; SELECT * FROM stu WHERE NOT sname IS NULL; | ||||
模糊查询 | 当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。 | 1.查询姓名由5个字母构成的学生记录 SELECT * FROM stu WHERE sname LIKE "_____"; 2.查询姓名由5个字母构成,并且第5个字母为“i”的学生记录 SELECT * FROM stu WHERE sname LIKE "____i" ; 3.查询姓名以“z”开头的学生记录 SELECT * FROM stu WHERE sname LIKE "z%" ; 4.查询姓名中第2个字母为“i”的学生记录 SELECT * FROM stu WHERE sname LIKE "_i%" ; 5.查询姓名中包含“a”字母的学生记录 SELECT * FROM stu WHERE sname LIKE "%a%" ; | 1.模糊查询必须使用LIKE关键字。其中 “_”匹配任意一个字母,5个“_”表示5个任意字母 3.其中“%”匹配0~n个任何字母。 | |||
字段控制查询 | 1.去重复:distinct 2.字段1+字段2 3.别名:as | 1.去除重复记录 SELECT DISTINCT sal FROM emp; 2.查看雇员的月薪与佣金之和 SELECT empno,ename,sal+comm FROM emp; SELECT empno,ename,sal+IFNULL(comm,0) FROM emp; 3.给列名添加别名 SELECT empno,ename, sal+IFNULL(comm,0) AS total FROM emp; SELECT *,sal+IFNULL(comm,0) total FROM emp; | 1. emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时,那么会出现重复记录,那么想去除重复记录,需要使用DISTINCT。 2.1 因为sal和comm两列的类型都是数值类型,所以可以做加运算。如果sal或comm中有一个字段不是数值类型,那么会出错。 2.2 comm列有很多记录的值为NULL,因为任何东西与NULL相加结果还是NULL,所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL。 3.在上面查询中出现列名为sal+IFNULL(comm,0),这很不美观,现在我们给这一列给出一个别名,为total。这个AS也可以省略。 | |||
排序 | ORDER BY | 1.查询所有学生记录,按年龄升序排序 SELECT * FROM stu ORDER BY age ASC; SELECT * FROM stu ORDER BY age ; 2.查询所有学生记录,按年龄降序排序 SELECT * FROM stu ORDER BY age DESC; 3.查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序 SELECT * FROM emp ORDER BY sal DESC,empno; | 注意:写在select语句的最后 | |||
聚合函数 | 聚合函数是用来做纵向运算的函数 | COUNT() 统计指定列不为NULL的记录行数; | 1.COUNT 当需要纵向统计时可以使用COUNT() 1.1 查询emp表中记录数: SELECT COUNT(1) FROM emp; SELECT COUNT(*) FROM emp; 1.2 查询emp表中有佣金的人数: SELECT COUNT(1) FROM emp WHERE comm IS NOT NULL; 1.3 查询emp表中月薪大于2500的人数: SELECT COUNT(1) FROM emp WHERE sal > 2500; 1.4 统计月薪与佣金之和大于2500元的人数: SELECT COUNT(1) FROM emp WHERE (sal + IFNULL(comm,0)) > 2500; 1.5 查询有佣金的人数,以及有领导的人数: SELECT COUNT(mgr) AS manager,COUNT(comm) AS comm FROM emp ; | |||
SUM(): 计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0; AVG(): 计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0; | 2.SUM和AVG 当需要纵向求和时使用sum()函数。 2.1l查询所有雇员月薪和: SELECT SUM(sal) FROM emp; 2.2l查询所有雇员月薪和,以及所有雇员佣金和: SELECT SUM(sal),SUM(comm) FROM emp; 2.3l查询所有雇员月薪+佣金和: SELECT SUM(sal)+SUM(comm) AS SUM FROM emp; SELECT SUM(sal + IFNULL(comm,0)) AS SUM FROM emp; 2.4l统计所有员工平均工资: SELECT SUM(sal)/COUNT(1) FROM emp; SELECT AVG(sal) FROM emp; | |||||
MAX() 计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算; MIN() 计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算; | 3.MAX和MIN 3.1 查询最高工资和最低工资: SELECT MAX(sal) MAX,MIN(sal) MIN FROM emp; | |||||
分组查询 | 按照字段的值分组 | GROUP BY 字段名 HAVING 字段名条件 判断的字段必须是分组字段或者聚合字段结果 聚合字段结果:用聚合函数统计的字段后的结果 | 1.查询每个部门的部门编号和每个部门的工资和: SELECT deptno 部门编号,SUM(sal) 工资和 FROM emp GROUP BY deptno; 2.查询每个部门的部门编号以及每个部门的人数: SELECT deptno 部门编号,COUNT(1) 人数 FROM emp GROUP BY deptno; 3.查询每个部门的部门编号以及每个部门工资大于1500的人数: SELECT deptno, COUNT(1) FROM emp WHERE sal > 1500 GROUP BY deptno ; 4.查询工资总和大于9000的部门编号以及工资和: SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000; SELECT deptno,SUM(sal) AS dsalsum FROM emp GROUP BY deptno HAVING dsalsum > 9000 ORDER BY dsalsum; | 1.注意:前面select的字段名只能是分组的字段名称,或者是聚合函数。比如:前面的字段名称只能是deptno 3.注意:where 是过滤普通所有数据的,逻辑:先过滤出大于1500的记录,然后分组统计数据 4.注意:having是过滤分组后,某字段的条件。 逻辑:先根据where条件过滤出需要处理的数据 然后更具分组进行分组,having是过滤分组后的列的条件的; 然后order BY 进行排序,当然这个字段是基于分组后的表,排序的字段当然不能是分组的字段和聚合统计的字段了。 | ||
条数查询 | 1.查询前几条数据 2.从第Start+1条数据开始,获取Len条数据 | LIMIT用来限定查询结果的起始行,以及总行数 Limit start len start:从0开始,表示第start+1条记录开始查询,包含第start+1条记录 len:查询记录的条数 | 1.查询5行记录,起始行从0开始 SELECT * FROM emp LIMIT 0 , 5; SELECT * FROM emp LIMIT 5; 2.查询10行记录,起始行从第3行开始 SELECT * FROM emp LIMIT 2 , 10; 3.分页查询,如果一页记录为10条: 第一页记录起始行为0,一共查询10行; SELECT * FROM emp LIMIT 0 , 10; 第二页记录起始行为10,一共查询10行; SELECT * FROM emp LIMIT 9 , 10; 第三页记录起始行为20,一共查询10行; SELECT * FROM emp LIMIT 19 , 10; |
多表查询
多表 | 合并结果集 | 把两个select语句的查询结果合并到 | | 1.去除重复记录 格式:(结果1)UNION (结果2) | 被合并的两个结果:列数、列类型必须相同 | 如图 | 1.在多表查询中,在使用列时必须指定列所从属的表,例如emp.deptno表示emp表的deptno列。 2. |
2.UNION ALL不去除重复记录 格式:(结果1)UNION (结果2) | 如图 | ||||||
连接查询 | 1.当需要的数据来源于多个表,那么就得使用连接查询 2.包括:内连接、外连接(左外连接,有外连接)、自然连接 | 内连接 | 1.(标准)SELECT * FROM emp e INNER JOIN dept d ON e.deptno = d.deptno; 2.(方言)SELECT * FROM emp e where e.deptno = d.deptno; | 1.内连接只会获取到两个表的交集 2.逻辑:先求出两个表的笛卡尔积,然后在使用where或者on条件过滤,在满足条件的结果中,根据select来提取需要的字段。 3.特点:结果数据都满足条件。 如:insert into emp(eid,ename,edptno) values(12,"aili",40); 如果dept表中没有40这个部门,那么这条记录将不会显示 4.INNER可以省略,MySQL默认的连接方式就是内连接 | create table student ( sid int, sname varchar(10), cid int ); create table college ( cid int, cname varchar(10) ); 1.获取笛卡尔积:select * from student,college; 2.使用内连接查看:select student.*,college.* from student inner join college on student.cid=college.cid; 3.使用内连接方言:select student.*,college.* from student,college where student.cid=college.cid; | ||
外连接_左外连接 | SELECT * FROM emp e left OUTER JOIN dept d ON e.deptno = d.deptno; 注:OUTER可以省略 | 1.左连接 | |||||
外连接_右外连接 | SELECT * FROM emp e right OUTER JOIN dept d ON e.deptno = d.deptno; 注:OUTER可以省略 | ||||||
自然连接 | 1.内连接:SELECT * FROM emp NATURAL JOIN dept; 2.左左连接:SELECT * FROM emp NATURAL LEFT JOIN dept; 3.右外连接:SELECT * FROM emp NATURAL RIGHT JOIN dept; | 1.两张连接的表中名称和类型完成一致的列作为条件,例如emp和dept表都存在deptno列,并且类型一致,所以会被自然连接找到 2.当然自然连接还有其他的查找条件的方式,但其他方式都可能存在问题 3.不建议使用 | |||||
嵌套查询 (子查询) | 1.一个查询需要另外一个查询的结果 2.涉及自连接;是查询字段在一个表,条件在另一个表中嵌套是个比较好的选择;两张表中反向查询需求 3.谓词:ANY、ALL、IN、EXISTS 4.子查询出现的位置: Ø where后,作为条件的一部分; Ø from后,作为被查询的一条表; 5.当子查询出现在where后作为条件时,还可以使用如下关键字: Ø any 如:> any() 表示大于任意一个就行,大于最小的也可以,即:>min Ø all 如:> all() 表示大于所有,比最大的大就是大于所有,即:>max in 如:in () 表示必须是其中的一个值,如同:=aa or = bb 6.子查询结果集的形式: Ø 单行单列(用于条件) Ø 单行多列(用于条件) Ø 多行单列(用于条件) Ø 多行多列(用于表) | 自连接嵌套 | 举例:查询工资大于员工平均工资的员工 逻辑:1.查询员工的平均工资 2.查询员工工资大于平均工资的员工 示例: select * from 员工表 where 工资>(平均工资) select avg(工资) from 员工表; | 举例:查询工资大于员工平均工资的员工 逻辑:1.查询员工的平均工资 3.查询员工工资大于平均工资的员工 示例: select * from 员工表 where 工资>(平均工资) select avg(工资) from 员工表; | select * from emp where sal > ( select avg(sal) from emp); | ||
查询字段一个表,条件在另一个表 | 举例:查询销售部和财务部的员工信息 逻辑:1.查询销售部和财务的部门编号 2.使用该部门编号查询员工信息 说明:该需求也可以使用连接查询进行 | 1.嵌套查询 和 连接查询一般可以相关转化:即可以用嵌套查询的,也可以使用连接查询进行替代;同样使用连接查询的sql语句一般都可以使用嵌套语句来实现。 | 1.select * from emp where deptno in ( select deptno from dept where dname in("销售部","财务部")); 2.select emp.* from emp,dept where emp.deptno = dept.deptno and (dname = "销售部" or dname="财务部"); | ||||
反向查询 | 举例;查询没有员工的部门 逻辑:遍历部门表,如果该部门编号在员工表中有,那么就证明白部门有员工,如果没有查询出结果,那么这个部门就没有员工。 | 1.使用谓词:EXISTS实现比较容易理解 2.使用外连接查询,使用IS NULL判断 | 1.select * SELECT * FROM dept WHERE NOT EXISTS ( SELECT * FROM emp WHERE emp.`deptno` = dept.`deptno`); 2.SELECT * FROM dept WHERE deptno NOT IN(SELECT DISTINCT deptno FROM emp); 3.SELECT d.*,e.* FROM emp e RIGHT OUTER JOIN dept d ON e.`deptno` = d.`deptno` WHERE e.`ename` IS NULL; |
用户
对象 | 功能 | 命令 | 示例 | 说明 | 注意 |
用户 | 创建用户 | CREATE USER 用户名@地址 IDENTIFIED BY '密码'; | CREATE USER user1@localhost IDENTIFIED BY ‘123’; | user1用户只能在localhost这个IP登录mysql服务器 | |
CREATE USER user2@’%’ IDENTIFIED BY ‘123’; | user2用户可以在任何电脑上登录mysql服务器 | ||||
给用户授权 | GRANT 权限1, … , 权限n ON 数据库.* TO 用户名 | GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost; | |||
GRANT ALL ON mydb1.* TO user2@localhost; | |||||
撤销授权 | REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名 | REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost; | |||
查看用户权限 | SHOW GRANTS FOR 用户名 | SHOW GRANTS FOR user1@localhost; | |||
删除用户 | DROP USER 用户名 | DROP USER user1@localhost; | |||
修改用户密码 | USE mysql; UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’; FLUSH PRIVILEGES; | UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2' and Host=’localhost’; FLUSH PRIVILEGES; |
外键
外键就是用来约束这一列的值必须是另一张表的主键值!!!
|
2.创建t_section表,指定sid为主键列,u_id为相对t_user表的uid列的外键:
|
3.修改t_section表,指定u_id为相对t_user表的uid列的外键:
|
4.修改t_section表,删除u_id的外键约束:
|
典型sql语句
编号 | 需求 | 实现 | 看点 |
1 | 列出薪金高于各部门平均薪金的所有员工信息,所在部门名称 | SELECT te.`ename`,td.`dname` ,te.`sal`,t.avgsal FROM emp te, (SELECT AVG(te.sal) avgsal, deptno FROM emp te GROUP BY deptno) t , dept td WHERE t.deptno = te.`deptno`AND td.`deptno` = te.`deptno` AND te.`sal` > t.avgsal ORDER BY te.`deptno`; | 1.select的结果作为数据表参与查询 |
2 | 谓词ANY、ALL可以使用聚集函数替代,且效率比谓词高 |
建表语句
说明:可以在一个数据库中直接复制过去执行,就可以产生如下几个表,还有测试数据;或者将该数据保存为一个sql文件,使用数据库还原的方式将数据还原
Sql脚本:
-- MySQL dump 10.13 Distrib 5.5.27, for Win32 (x86)
--
-- Host: localhost Database: wqdb1
-- ------------------------------------------------------
-- Server version 5.5.27
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `dept`
--
DROP TABLE IF EXISTS `dept`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dept` (
`deptno` int(11) DEFAULT NULL,
`dname` varchar(14) DEFAULT NULL,
`loc` varchar(13) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `dept`
--
LOCK TABLES `dept` WRITE;
/*!40000 ALTER TABLE `dept` DISABLE KEYS */;
INSERT INTO `dept` VALUES (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');
/*!40000 ALTER TABLE `dept` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `emp`
--
DROP TABLE IF EXISTS `emp`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `emp` (
`empno` int(11) DEFAULT NULL,
`ename` varchar(50) DEFAULT NULL,
`job` varchar(50) DEFAULT NULL,
`mgr` int(11) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(7,2) DEFAULT NULL,
`comm` decimal(7,2) DEFAULT NULL,
`deptno` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `emp`
--
LOCK TABLES `emp` WRITE;
/*!40000 ALTER TABLE `emp` DISABLE KEYS */;
INSERT INTO `emp` VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),(7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30),(7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30),(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000.00,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30),(7876,'ADAMS','CLERK',7788,'1987-05-23',1100.00,NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10);
/*!40000 ALTER TABLE `emp` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `stu`
--
DROP TABLE IF EXISTS `stu`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `stu` (
`sid` char(6) DEFAULT NULL,
`sname` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `stu`
--
LOCK TABLES `stu` WRITE;
/*!40000 ALTER TABLE `stu` DISABLE KEYS */;
INSERT INTO `stu` VALUES ('S_1001','liuYi',35,'male'),('S_1002','chenEr',15,'female'),('S_1003','zhangSan',95,'male'),('S_1004','liSi',65,'female'),('S_1005','wangWu',55,'male'),('S_1006','zhaoLiu',75,'female'),('S_1007','sunQi',25,'male'),('S_1008','zhouBa',45,'female'),('S_1009','wuJiu',85,'male'),('S_1010','zhengShi',5,'female'),('S_1011','xxx',NULL,NULL);
/*!40000 ALTER TABLE `stu` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2015-05-09 15:51:20
END