文章目录
外连接
- 左外连接（如果左侧的表完全显示我们就说是左外连接）
- 右外连接（如果右侧的表完全显示我们就说是右外连接）
CREATE TABLE stu(
id INT,
`name` VARCHAR(32));
DELETE FROM stu;
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
SELECT * FROM stu;
CREATE TABLE exam(
id INT,
grade INT);
INSERT INTO exam VALUES(1,56),(2,76),(11,8);
DELETE FROM exam;
SELECT * FROM exam;
-- 左连接，显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号,成绩显示为空
SELECT `name`,stu.id,grade
FROM stu LEFT JOIN exam
ON stu.id = exam.id;
-- 右连接，显示所有成绩，如果没有名字匹配，显示空
SELECT `name`,exam.id,grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id;
-- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT dname,emp.*
FROM emp RIGHT JOIN dept
ON emp.deptno = dept.deptno;
SELECT dname,emp.*
FROM dept LEFT JOIN emp
ON emp.deptno = dept.deptno;
mysql 约束
- 约束用于确保数据库数据满足特定的商业规则。在mysql中，约束包括：not null、unique、primary key、foreign key 和check五种。
primary key 主键
- 字段名 字段类型 primary key
- 用于唯一的标示表行的数据，当定义主键约束后，该列不能重复。
细节
CREATE TABLE t16(
id INT PRIMARY KEY,
`name` VARCHAR(32),
email VARCHAR(32));
INSERT INTO t16
VALUES(1,'jack','jack@qq.com');
INSERT INTO t16
VALUES(2,'marry','marry@qq.com');
INSERT INTO t16
VALUES(1,'tom','tom@qq.com');-- 发生错误
SELECT * FROM t16;
-- primary key 不能重复也不能为空
INSERT INTO t16
VALUES(NULL,'tom','tom@qq.com');-- 发生错误
-- 一张表最多只能有一个主键，但可以是复合主键
CREATE TABLE t17(
id INT PRIMARY KEY,
`name` VARCHAR(32) PRIMARY KEY,
email VARCHAR(32));-- 发生错误
CREATE TABLE t17(
id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(id, `name`)
); -- 复合主键 id和name都相同才违反主键
-- 主键的指定方式有两种
-- 直接在字段名后面指定，字段名 primary key
-- 在表定义最后写primary key(列名)
-- 使用desc 表名，可以查看到primary key 的情况
not null(非空)和unique(唯一)
- not null(非空)
-
- 如果在列上定义了not null，那么当插入数据时，必须为列提供数据。
-
- 字段名 字段类型 not null
- unique(唯一)
-
- 当定义了唯一约束后，该列值是不能重复的。
- 字段名 字段类型 unique
- 如果没有指定not null，则unique字段可以有多个null
- 一张表可以有多个unique字段
- unique not null 等价于 primary 主键
CREATE TABLE t18(
id INT UNIQUE,
`name` VARCHAR(32),
email VARCHAR(32));
SELECT * FROM t18;
INSERT INTO t18
VALUES(NULL,'jojo','jojo@qq.com');-- 可以
INSERT INTO t18
VALUES(NULL,'haha','haha@qq.com');-- 可以
INSERT INTO t18
VALUES(1,'xixi','xixi@qq.com');-- 可以
INSERT INTO t18
VALUES(1,'lele','lele@qq.com');-- 不可以
CREATE TABLE t19(
id INT UNIQUE,
`name` VARCHAR(32) UNIQUE,
email VARCHAR(32)); -- 可以
foreign key(外键)
- 用于定于主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null
- foreign key (本表字段名) references
- 主表名(主键名或unique字段名)
-- 创建班级表---主表
CREATE TABLE my_class(
id INT PRIMARY KEY, -- 主表则必须具有主键约束或是unique约束,防止多个相同id的记录
nam VARCHAR(32) NOT NULL DEFAULT '');
CREATE TABLE my_stu(
id INT PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT '',
class_id INT,
FOREIGN KEY(class_id) REFERENCES my_class(id));
INSERT INTO my_class
VALUES(100,'java'),(200,'web');
SELECT * FROM my_class;
INSERT INTO my_stu
VALUES(1,'tom',100);-- 成功
INSERT INTO my_stu
VALUES(2,'jack',200);-- 成功
INSERT INTO my_stu
VALUES(3,'marry',300);-- 失败，因为my_class中不存在300班级
INSERT INTO my_stu
VALUES(3,'marry',null);-- 成功 外键字段的值，必须在主键字段中出现过，或者为null 【前提是外键字段允许为null】
SELECT * FROM my_stu;
细节说明
- 外键指向的表的字段，要求是primary key或者是 unique;
- 表的类型是innodb，这样的表才支持外键
- 外键字段的类型要和主键字段的类型一致（长度可以不同）
- 外键字段的值，必须在主键字段中出现过，或者为null 【前提是外键字段允许为null】
- 一旦建立主外键关系，数据不能随意删除了
check
- 用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在10002000之间如果不再10002000之间就会提示出错。
- oracle 和 sql server 均支持check，但是mysql5.7目前还不支持check，只做语法校验，但不会生效
- 基本语法： 列名 类型 check (check条件)
CREATE TABLE t23(
id INT PRIMARY KEY,
`name` VARCHAR(32),
sex VARCHAR(6) CHECK (sex IN('man','woman')),
sal DOUBLE CHECK (sal > 1000 AND sal < 2000)
);
CREATE DATABASE shop_db;
DROP TABLE goods;
DROP TABLE customer;
CREATE TABLE goods(
goods_id INT PRIMARY KEY,
goods_name VARCHAR(32) NOT NULL DEFAULT '',
unitprice DECIMAL(10,2) NOT NULL DEFAULT 0
CHECK (unitprice > 1.0 AND unitprice < 9999.99),
category INT NOT NULL DEFAULT 0,
provider VARCHAR(32) NOT NULL DEFAULT '');
CREATE TABLE customer(
customer_id INT PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT '',
address VARCHAR(32) NOT NULL DEFAULT '',
email VARCHAR(32) UNIQUE,
sex ENUM('男','女') NOT NULL,
-- sex char(1) check (sex in('男','女')),
card_id CHAR(18));
CREATE TABLE purchase(
order_id INT PRIMARY KEY,
customer_id INT,
goods_id INT,
nums INT NOT NULL DEFAULT 0,
FOREIGN KEY(customer_id) REFERENCES customer(customer_id),
FOREIGN KEY(goods_id) REFERENCES goods(goods_id));
自增长
- 在某张表中，存在一个id列（整数），希望在添加记录的时候该列从1开始，自动的增长。
- 字段名 整型 primary key auto_increment
- 添加自增长的字段方式
-
- insert into XXX (字段1，字段2…) values(null，‘值’…);
- insert into XXX (字段2…) values(‘值1’，‘值2’…);
- insert into XXX values(null，‘值1’…);
CREATE TABLE t1(
id INT PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(32) NOT NULL DEFAULT '');
SELECT * FROM t1;
INSERT INTO t1 VALUES(NULL,'1'); -- 1 1
INSERT INTO t1 (tname) VALUES('2');-- 2 2
INSERT INTO t1 VALUES(8,'3'); -- 8 3
INSERT INTO t1 VALUES(NULL,'4'); -- 9 4
使用细节
- 一般来说自增长是和primary key配合使用的
- 自增长也可以单独使用【但是需要配合一个unique】
- 自增长修饰的字段为整数型的（虽然小数也可以但非常少这样使用）
- 自增长默认从1开始，你也可以通过如下命令修改alter table 表名 auto_increment = xxx;
- 如果你添加数据时，给自增长字段（列）指定的有指，则以指定的值为准。
CREATE TABLE t2(
id INT UNIQUE AUTO_INCREMENT,
tname VARCHAR(32) NOT NULL DEFAULT '');
INSERT INTO t2 VALUES(NULL,'1'); -- 1 1
INSERT INTO t2 (tname) VALUES('2');-- 2 2
INSERT INTO t2 VALUES(8,'3'); -- 8 3
INSERT INTO t2 VALUES(NULL,'4'); -- 9 4
SELECT * FROM t2;
ALTER TABLE t2 AUTO_INCREMENT = 88;
INSERT INTO t2 VALUES(NULL,'6'); -- 88 6
mysql 索引
- 索引：提高数据库性能，不用加内存，不用改程序，不用调sql，查询速度就可能提高百倍千倍。
-- 在没有创建索引时，我们查询一条记录
select *
from emp
where empno = 1234567; -- 4.5s --> 0.003s
-- 使用索引来优化一下，体验索引的牛
-- 在没有创建索引前，emp.ibd文件大小是 524M
-- 在创建索引后 emp.ibd文件大小是655M【索引本身也会占用空间】
-- empno_index 索引名称
-- ON emp (empno) :表示在emp表的empno列创建索引
create index empno_index on emp (empno)
-- 创建索引后，只对创建索引的列起作用
- 索引的代价
-
- 磁盘占用
- 对dml(update delete insert)语句的效率影响 因为会对索引进行维护，会对速度有影响
- 索引的原理
| 1[id] | jack[name] | 20[age] |
|---|---|---|
| 2 | tom | 10 |
| 3 | tom2 | |
| 4 | tom3 | |
| 5 | tom4 | |
| 6 | tom5 | |
| 7 | tom6 | |
| 8 | tom7 | |
| 9 | tom8 |
当我们没有索引时
select * from emp where id = 9; 进行全表扫描，即使id = 1时，也会进行全表扫描，防止下面还有id = 1的记录。
当我们创建索引后，建立一个数，拿出中间的数为头，将比该数大的数放右边，将比该数小的数放左边，建树，形成二叉树。
索引使用
-
索引的类型
-
- 主键索引，主键自动的为主索引（类型 primary）
- 唯一做因 （unique）
- 普通索引（index）
- 全文索引（felltext）【适用于MyISAM】
-
- 开发中考虑使用，一般不使用mysql自带的，全文搜索Solr 和 ElasticSearch（ES）
-
索引使用
-
-
添加索引
-
- create [unique] index index_name on tbl_name(col_name[(length)] [asc | desc],…)
- alter table table_name ADD index [index_name] (index_col_name,…)
-
添加主键（索引）
-
- alter table table_name ADD primary key(列名,…);
-
删除索引
-
- drop index index_name on tbl_name;
- alter table table_name drop index index_name;
-
删除主键索引
-
- alter table t_b drop primary key;
-
-- 演示mysql的索引的使用
-- 创建索引
CREATE TABLE t25 (
id INT ,
`name` VARCHAR(32));
-- 查询表是否有索引
SHOW INDEXES FROM t25;
-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t25 (id);
-- 添加普通索引方式1
CREATE INDEX id_index ON t25 (id);
-- 如何选择
-- 1. 如果某列的值，是不会重复的，则优先考虑使用unique索引, 否则使用普通索引
-- 添加普通索引方式2
ALTER TABLE t25 ADD INDEX id_index (id)
-- 添加主键索引
CREATE TABLE t26 (
id INT ,
`name` VARCHAR(32));
ALTER TABLE t26 ADD PRIMARY KEY (id)
SHOW INDEX FROM t26;
-- 删除索引
drop index id_index on t25;
-- 删除主键索引
alter table t26 drop primary key;
-- 修改索引，先删除在添加新的索引
-- 查询索引
-- 1
show index from t25;
-- 2
show indexes from t25;
-- 3
show keys from t25;
-- 4
desc t25;
小结
- 那些列上适合使用索引
-
- 较频繁的作为查询条件字段应该创建索引
- 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
- 更新非常频繁的字段不适合创建索引
- 不会出现在where子句中字段不该创建索引
事务
- 事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性。
- 事务和锁
-
- 当执行事务操作时（dml语句），MySQL会在表上加锁，防止其他用户改表的数据，这对用户来说是非常重要的。
- mysql数据库控制台事务的几个重要操作
-
- start transaction 开始一个事务
- savepoint 保存点名—设置保存点
- rollback to 保存点名—回退事务
- rollback 回退全部事务
- commit 提交事务，所有的操作生效，不能回退
- 细节
- 没有设置保存点
- 多个保存点
- 存储引擎
- 开始事务方式
-- 事务的一个重要的概念和具体操作
-- 看一个图[看示意图]
-- 演示
-- 1. 创建一张测试表
CREATE TABLE t27
( id INT,
`name` VARCHAR(32));
-- 2. 开始事务
START TRANSACTION
-- 3. 设置保存点
SAVEPOINT a
-- 执行dml 操作
INSERT INTO t27 VALUES(100, 'tom');
SELECT * FROM t27;
SAVEPOINT b
-- 执行dml操作
INSERT INTO t27 VALUES(200, 'jack');
-- 回退到 b
ROLLBACK TO b
-- 继续回退 a
ROLLBACK TO a
-- 如果这样, 表示直接回退到事务开始的状态.
ROLLBACK -- 还可以到b吗？？？不可以回退到某点后，之后的保存点会被删掉
COMMIT -- 保存点删除，没有机会回退，没有后悔药了
-
回退事务
-
- 在介绍回退事务前，先介绍一下保存点（savepoint）。保存点是事务中的点，用于取消部分事务，当结束事务时，会自动删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的点。
-
提交事务
-
- 使用commit语句可以提交事务，当执行了commit语句子后，会确认事务的变化、结束事务、删除保存点、释放锁、数据生效。当使用commit语句结束事务子后，其他会话【其他连接】将可以查看到事务变化后的新数据【所有的数据正式生效】。
-
事务细节
-
- 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
- 如果开始一个事务，你没有创建保存点，你可以执行rollback，默认就是回滚到你事务开始的状态
- 你也可以在这个事务中（还没有提交时）创建多个保存点，比如 savepoint aaa,指定dml，savepoint bbb
- 你可以在事务没有提交前，选择回退到哪个保存点
- Mysql的事务机制需要innodb的存储引擎才可以使用，myisam不好使
- 开始一个事务start transaction， set autocommit = off
-- 讨论 事务细节
-- 1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
INSERT INTO t27 VALUES(300, 'milan'); -- 自动提交 commit
SELECT * FROM t27
-- 2. 如果开始一个事务，你没有创建保存点. 你可以执行 rollback，
-- 默认就是回退到你事务开始的状态
START TRANSACTION
INSERT INTO t27 VALUES(400, 'king');
INSERT INTO t27 VALUES(500, 'scott');
ROLLBACK -- 表示直接回退到事务开始的的状态
COMMIT;
-- 3. 你也可以在这个事务中(还没有提交时), 创建多个保存点.比如: savepoint aaa;
-- 执行 dml , savepoint bbb
-- 4. 你可以在事务没有提交前，选择回退到哪个保存点
-- 5. InnoDB 存储引擎支持事务 , MyISAM 不支持
-- 6. 开始一个事务 start transaction, set autocommit=off;
隔离
- 事务隔离级别
-
- 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性
- 如果不考虑隔离性，可能会引发如下问题
-
- 脏读 dirty read
-
- 当一个事务读取另一个事务尚未提交的改变（update,insert,delete）时，产生脏读
- 不可重复读 nonrepeatable read
-
- 同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
- 幻读 phantom read
-
- 同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。
- 查看事务的隔离级别
-
- 查看当前会话隔离级别 select @@tx_isolation;
- 查看系统当前隔离级别
-
- select @@global.tx_isolation;
- 设置当前会话隔离级别
-
- set session transaction isolation level repeatable read;
- 设置系统当前隔离级别
-
- set global transaction isolation level repeatable read;
- mysql默认的事务隔离级别是repeatable read,一般情况下，没有特殊要求，没有必要修改（因为该级别可以满足绝大部分项目需求）
- 全局修改，修改mysql.ini配置文件，在最后加上
-
- #可选参数有：READ-UNCOMMITTED，READ-COMMITTED，REPEATABLE-READ，SERIALIZABLE.
- [mysqld]
- transaction-isolation = REPEATABLE-READ
事务的acid特性
- 原子性（atomicity）
-
- 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
- 一致性（consistency）
-
- 事务必须使数据库从一个一致性状态变换到另外一个一致性状态
- 隔离性（isolation）
-
- 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
- 持久性（durability）
-
- 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
-- 登录mysql控制客户端A，创建表Dog（id，name)，开始一个事务，添加两条记录
-- 登录mysql控制客户端B，开始一个事务，设置为读未提交
-- A客户端修改Dog一条记录，不要提交。看看B客户端是否看到变化？
-- 登录mysql客户端C，开始一个事务，设置为读已提交，
-- 这时A客户修改一条记录，不要提交，看看C客户端是否看到变化？
存储引擎
- Mysql的表类型由存储引擎 storage engines决定，主要包括 MyISAM、innoDB、Memory等
- Mysql数据表主要支持六种类型，分别是CSV，Memory，ARCHIVE，MRG MYISAM，MYISAM，InnoBDB
- 这六种又分为两类，一类是‘’事务安全型‘’ transaction safe，比如innoDB；其余都属于第二类，称为 非事务安全型 non-transaction safe【mysiam 和 memory】
- 显示当前数据库支持的存储引擎
-
- show engines;
- 修改存储引擎
-
- alter table 表名 engine = 存储引擎
三种存储引擎
- MyISAM 不支持事务，也不支持外键，但其访问速度快，对事务完整性没有要求
- InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
- MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它得数据是放在内存中得，并且默认使用HASH索引。但是一旦服务关闭，表中得数据就会丢失掉，表得结构还在。
- 如何选择表的引擎
-
- 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快
- 如果需要支持事务，选择InnoDB
- Memory存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失（经典用法 用户的在线状态（）。）
-- 表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES
-- innodb 存储引擎，是前面使用过.
-- 1. 支持事务 2. 支持外键 3. 支持行级锁
-- myisam 存储引擎
CREATE TABLE t28 (
id INT,
`name` VARCHAR(32)) ENGINE MYISAM
-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁
START TRANSACTION;
SAVEPOINT t1
INSERT INTO t28 VALUES(1, 'jack');
SELECT * FROM t28;
ROLLBACK TO t1
-- memory 存储引擎
-- 1. 数据存储在内存中[关闭了Mysql服务，数据丢失, 但是表结构还在]
-- 2. 执行速度很快(没有IO读写) 3. 默认支持索引(hash表)
CREATE TABLE t29 (
id INT,
`name` VARCHAR(32)) ENGINE MEMORY
DESC t29
INSERT INTO t29
VALUES(1,'tom'), (2,'jack'), (3, 'hsp');
SELECT * FROM t29
-- 指令修改存储引擎
ALTER TABLE `t29` ENGINE = INNODB
视图
-
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表）
-
视图和基表关系的示意图
-
视图的基本使用
-
- create view 视图名 as select语句
- alter view 视图名 as select语句
- show create view 视图名
- drop view 视图名1，视图名2
-- 视图的使用
-- 创建一个视图emp_view01，只能查询emp表的(empno、ename, job 和 deptno ) 信息
-- 创建视图
CREATE VIEW emp_view01
AS
SELECT empno, ename, job, deptno FROM emp;
-- 查看视图
DESC emp_view01
SELECT * FROM emp_view01;
SELECT empno, job FROM emp_view01;
-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01
-- 删除视图
DROP VIEW emp_view01;
-- 视图的细节
-- 1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm)
-- 2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]
-- 修改视图 会影响到基表
UPDATE emp_view01
SET job = 'MANAGER'
WHERE empno = 7369
SELECT * FROM emp; -- 查询基表
SELECT * FROM emp_view01
-- 修改基本表， 会影响到视图
UPDATE emp
SET job = 'SALESMAN'
WHERE empno = 7369
-- 3. 视图中可以再使用视图 , 比如从emp_view01 视图中，选出empno,和ename做出新视图
DESC emp_view01
CREATE VIEW emp_view02
AS
SELECT empno, ename FROM emp_view01
SELECT * FROM emp_view02
- 安全
-
- 一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
- 性能
-
- 关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接JOIN。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
- 灵活
-
- 如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
管理
用户管理
-
mysql中的用户，都存储在系统数据库mysql中的user表中。
-
user表中的重要字段
-
- host : 允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址，比如192.168.1.100
- user ：用户名
- authentication_string : 密码，是通过mysql的password()函数加密之后的密码
-
创建用户
-
- create user ‘用户名’ @ ‘允许登录位置’ identified by ‘密码’
-
- 说明：创建用户，同时指定密码
-
删除用户
-
- drop user ‘用户名’ @ ‘允许登录位置’；
-
用户修改密码
-
- 修改自己的密码：
-
- set password = password(‘密码’)；
- 修改他人的密码（需要有修改用户密码权限）
-
- set password for ‘用户名’ @ ‘登录位置’ = password(‘密码’);
-- Mysql用户的管理
-- 原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的Mysql操作权限
-- 所以，Mysql数据库管理人员(root), 根据需要创建不同的用户，赋给相应的权限，供人员使用
-- 1. 创建新的用户
-- 解读 (1) 'hsp_edu'@'localhost' 表示用户的完整信息 'hsp_edu' 用户名 'localhost' 登录的IP
-- (2) 123456 密码, 但是注意 存放到 mysql.user表时，是password('123456') 加密后的密码
-- *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
CREATE USER 'hsp_edu'@'localhost' IDENTIFIED BY '123456'
SELECT `host`, `user`, authentication_string
FROM mysql.user
-- 2. 删除用户
DROP USER 'hsp_edu'@'localhost'
-- 3. 登录
-- root 用户修改 hsp_edu@localhost 密码, 是可以成功.
SET PASSWORD FOR 'hsp_edu'@'localhost' = PASSWORD('123456')
-- 修改自己的密码, 没问题
SET PASSWORD = PASSWORD('abcdef')
-- 修改其他人的密码， 需要权限
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456')
权限管理
-- 演示 用户权限的管理
-- 创建用户 shunping 密码 123 , 从本地登录
CREATE USER 'shunping'@'localhost' IDENTIFIED BY '123'
-- 使用root 用户创建 testdb ,表 news
CREATE DATABASE testdb
CREATE TABLE news (
id INT ,
content VARCHAR(32));
-- 添加一条测试数据
INSERT INTO news VALUES(100, '北京新闻');
SELECT * FROM news;
-- 给 shunping 分配查看 news 表和 添加news的权限
GRANT SELECT , INSERT
ON testdb.news
TO 'shunping'@'localhost'
-- 可以增加update权限
GRANT UPDATE
ON testdb.news
TO 'shunping'@'localhost'
-- 修改 shunping的密码为 abc
SET PASSWORD FOR 'shunping'@'localhost' = PASSWORD('abc');
-- 回收 shunping 用户在 testdb.news 表的所有权限
REVOKE SELECT , UPDATE, INSERT ON testdb.news FROM 'shunping'@'localhost'
REVOKE ALL ON testdb.news FROM 'shunping'@'localhost'
-- 删除 shunping
DROP USER 'shunping'@'localhost'
细节
- 在创建用户的时候，如果不指定Host，则为%，%表示所有IP都有连接权限 create user xxx;
- 也可以这样指定：create user ‘xxx’@‘192.168.1.%’ 表示 xxx用户在192.168.1.*的ip可以登录mysql
- 在删除用户的时候，如果host 不是%，需要明确指定 ‘用户’@‘host值’
-- 说明 用户管理的细节
-- 在创建用户的时候，如果不指定Host, 则为% , %表示表示所有IP都有连接权限
-- create user xxx;
CREATE USER jack
SELECT `host`, `user` FROM mysql.user
-- 你也可以这样指定
-- create user 'xxx'@'192.168.1.%' 表示 xxx用户在 192.168.1.*的ip可以登录mysql
CREATE USER 'smith'@'192.168.1.%'
-- 在删除用户的时候，如果 host 不是 %, 需要明确指定 '用户'@'host值'
DROP USER jack -- 默认就是 DROP USER 'jack'@'%'
DROP USER 'smith'@'192.168.1.%'
本章作业
select empno,ename name,sal salary from emp;
select empno,ename as name,sal as salary from emp;
-- as可以要可以不要。
-- 别名不可以有空格
-- 查看DEPT表和EMP表的结构的sql语句
DESC dept;
DESC emp;
-- 显示所有部门名称
SELECT * FROM emp;
SELECT dname FROM dept;
-- 显示所有雇员名及其全年收入 13月（工资+补助），并指定列别名“年收入”
SELECT ename,13*(sal+IFNULL(comm,0)) '年收入' FROM emp;
-- 显示工资超过2850的雇员姓名和工资
SELECT ename,sal FROM emp
WHERE sal > 2850;
-- 显示工资不在1500到2850之间的所有雇员名及工资
SELECT ename,sal FROM emp
WHERE sal NOT BETWEEN 1500 AND 2850;
-- 显示编号为7566的雇员姓名及所在部门编号
SELECT ename,deptno FROM emp
WHERE empno = 7566;
-- 显示部门10和30中工资超过1500的雇员名及工资
SELECT ename,sal,deptno FROM emp
WHERE deptno IN (10,30) AND sal > 1500;
-- 显示无管理者的雇员名及岗位
SELECT ename,job FROM emp
WHERE mgr IS NULL;
-- 显示在1991年2月1日到1991年5月1日之间雇用的雇员名，岗位及雇佣日期，并以雇佣日期进行排序
SELECT ename,job,hiredate FROM emp
WHERE hiredate BETWEEN '1991-02-01' AND '1991-05-01'
ORDER BY hiredate;
-- 显示获得补助的所有雇员名，工资及补助，并以工资降序排序
SELECT ename,sal,comm FROM emp
WHERE comm IS NOT NULL
ORDER BY sal DESC;
-- 找出部门30中的所有员工
SELECT * FROM emp
WHERE deptno = 30;
-- 列出所有办事员的姓名编号和部门编号
SELECT ename,empno,deptno FROM emp
WHERE job = 'CLERK';
-- 找出佣金高于薪金的员工
SELECT * FROM emp
WHERE IFNULL(comm,0) > sal;
-- 找出佣金高于薪金60%的员工
SELECT * FROM emp
WHERE IFNULL(comm,0) > sal * 0.6;
-- 找出部门10中所有经理和部门20中所有办事员
SELECT * FROM emp
WHERE (deptno = 10 AND job = 'MANAGER')
OR (deptno = 20 AND job = 'CLERK');
-- 找出部门10中所有经理和部门20中所有办事员,还有既不是经理又不是办事员但其薪金大于或等于2000的所有员工
SELECT * FROM emp
WHERE (deptno = 10 AND job = 'MANAGER')
OR (deptno = 20 AND job = 'CLERK')
OR (job <> 'MANAGER' AND job <> 'CLERK' AND sal >= 2000);
-- 找出收取佣金的员工的不同工作
SELECT DISTINCT job FROM emp
WHERE comm IS NOT NULL;
-- 找出不收取佣金或收取的佣金低于100的员工
SELECT * FROM emp
WHERE IFNULL(comm,0) < 100;
-- 找出各月倒数第3天受雇的所有员工
-- -----------------LAST_DAY()可以返回该日期所在月份的最后一天
SELECT * FROM emp
WHERE hiredate = DATE_SUB(LAST_DAY(hiredate),INTERVAL 2 DAY);
-- 找出早于12年前受雇的员工
SELECT * FROM emp
WHERE hiredate < DATE_SUB(NOW(),INTERVAL 12 YEAR);
-- 以首字母小写的方式显示所有员工的姓名
SELECT CONCAT(LCASE(LEFT(ename,1)),SUBSTRING(ename,2)) FROM emp;
-- 显示正好为5个字符的员工的姓名
SELECT ename FROM emp
WHERE LENGTH(ename) = 5;
-- 显示不带有R的员工的姓名
SELECT ename FROM emp
WHERE ename NOT LIKE '%R%';
-- 显示所有员工姓名的前三个字符
SELECT LEFT(ename,3) FROM emp;
-- 显示所有员工的姓名，用a替换所有的A
SELECT REPLACE(ename,'A','a') FROM emp;
-- 显示满10年服务年限的员工的姓名和受雇日期
SELECT ename,hiredate FROM emp
WHERE hiredate < DATE_SUB(NOW(),INTERVAL 10 YEAR);
-- 显示员工的详细资料，按姓名排序
SELECT * FROM emp
ORDER BY ename;
-- 显示员工的姓名和受雇日期根据服务年限将老员工排在前面
SELECT ename,hiredate,DATEDIFF(NOW(),hiredate) 'years' FROM emp
ORDER BY years DESC;
-- 显示所有员工的姓名工作薪金，按工作排，工作同按薪金排
SELECT ename,job,sal FROM emp
ORDER BY job DESC,sal;
-- 显示员工姓名、加入公司的年份和月份，按月份排，月份同按年份排
SELECT ename,YEAR(hiredate) `year`,MONTH(hiredate) `month` FROM emp
ORDER BY `month`,`year`;
-- 显示一个月工作30天情况下员工日薪，忽略余数
SELECT ename,FLOOR(sal/30) FROM emp;
-- 找出在2月入职的员工
SELECT * FROM emp
WHERE MONTH(hiredate) = 2;
-- 求员工加入公司的天数
SELECT ename,DATEDIFF(NOW(),hiredate) AS days FROM emp;
-- 显示姓名字段的任何位置包含A的员工
SELECT ename FROM emp
WHERE ename LIKE '%A%';
-- 以年月日的方式显示所有员工的服务年限
SELECT ename,FLOOR(DATEDIFF(NOW(),hiredate)/365) AS years,
FLOOR((DATEDIFF(NOW(),hiredate)%365)/30) AS months,
(DATEDIFF(NOW(),hiredate)%356)%30 AS days,
FROM_DAYS(DATEDIFF(NOW(),hiredate))
FROM emp;
SELECT ename,FROM_DAYS(DATEDIFF(NOW(),hiredate)) FROM emp;
-- 列出至少有一个员工的所有部门
SELECT dname,c FROM dept,(SELECT deptno,COUNT(ename) c FROM emp
GROUP BY deptno
HAVING c > 1) counts
WHERE dept.deptno = counts.deptno
-- 列出薪金比SMITH多的员工
SELECT * FROM emp
WHERE sal > (SELECT sal FROM emp
WHERE ename = 'SMITH'
)
-- 列出受雇日期晚于其上级的员工
SELECT * FROM emp worker,emp boss
WHERE worker.mgr = boss.empno
AND worker.hiredate > boss.hiredate
-- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT dname,emp.* FROM
dept LEFT JOIN emp
ON dept.deptno = emp.deptno
-- 列出所有CLERK的姓名及其部门名称
SELECT ename,job,dname FROM dept,emp
WHERE dept.deptno = emp.deptno
AND job = 'CLERK'
-- 列出最低薪金大于1500的各种工作
SELECT job,MIN(sal) AS min_sal FROM emp
GROUP BY job
HAVING min_sal > 1500
-- 列出部门SALES工作的员工的姓名
SELECT * FROM emp
WHERE deptno = (SELECT deptno FROM dept
WHERE dname = 'SALES'
)
-- 列出薪金高于公司平均薪金的所有员工
SELECT * FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp
)
-- 列出与SCOTT从事相同工作的人
SELECT * FROM emp
WHERE job = (SELECT job FROM emp
WHERE ename = 'SCOTT'
)
-- 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金
SELECT ename,sal FROM emp
WHERE sal > ALL(
SELECT sal FROM emp
WHERE deptno = 30
)
-- 列出在每个部门工作的员工数量、平均工资和平均服务年限
SELECT deptno,COUNT(ename),AVG(sal),FORMAT(AVG(FLOOR(DATEDIFF(NOW(),hiredate)/365)),2) FROM emp
GROUP BY deptno
-- 列出所有员工的姓名部门名称和工资
SELECT ename,dname,sal FROM dept,emp
WHERE dept.deptno = emp.deptno;
-- 列出所有部门的详细信息和部门人数
SELECT dept.*,IFNULL(counts,0) AS '总人数' FROM dept
LEFT JOIN (SELECT deptno,COUNT(ename) counts FROM emp
GROUP BY deptno) renshu
ON dept.deptno = renshu.deptno
-- 列出各种工作的最低工资
SELECT job,MIN(sal + IFNULL(comm,0)) AS '最低工资' FROM emp
GROUP BY job
-- 列出MANAGER的最低薪金
SELECT job,MIN(sal + IFNULL(comm,0)) AS '最低工资' FROM emp
GROUP BY job
HAVING job = 'MANAGER'
-- 列出所有员工的年工资，按年薪从低到高排序
SELECT ename,12*(sal+IFNULL(comm,0)) AS annul_sal FROM emp
ORDER BY annul_sal;
CREATE TABLE class(
classid INT PRIMARY KEY,
`subject` VARCHAR(64) NOT NULL DEFAULT '',
deptname VARCHAR(64) NOT NULL DEFAULT '',
enrolltime CHAR(4) NOT NULL,
num INT NOT NULL DEFAULT 0,
FOREIGN KEY(deptname) REFERENCES department(deptname))
INSERT INTO class VALUES(101,'软件','计算机',1995,20),
(102,'微电子','计算机',1996,30),
(111,'无机化学','化学',1995,29),
(112,'高分子化学','化学',1996,25),
(121,'统计数学','数学',1995,20),
(131,'现代语言','中文',1996,20),
(141,'国际贸易','经济',1997,30),
(142,'国际金融','经济',1996,14);
DROP TABLE class
DROP TABLE student
CREATE TABLE student(
studentid INT PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT '',
age INT,
classid INT,
FOREIGN KEY(classid) REFERENCES class(classid))
INSERT INTO student VALUES(8101,'张三',18,101),
(8102,'钱四',16,121),
(8103,'王玲',17,131),
(8105,'李飞',19,102),
(8109,'赵四',18,141),
(8110,'李可',20,142),
(8201,'张飞',18,111),
(8302,'周瑜',16,112),
(8203,'王亮',17,111),
(8305,'董庆',19,102),
(8409,'赵龙',18,101),
(8510,'李丽',20,142);
CREATE TABLE department(
departmentid INT PRIMARY KEY,
deptname VARCHAR(64) UNIQUE NOT NULL DEFAULT '')
INSERT INTO department VALUES(001,'数学'),(002,'计算机'),(003,'化学'),(004,'中文'),(005,'经济');
SELECT * FROM department
SELECT * FROM class
SELECT * FROM student
SELECT * FROM student
WHERE `name` LIKE '李%'
SELECT deptname,COUNT(`subject`) AS counts FROM class
GROUP BY deptname
HAVING counts > 1
SELECT department.*,sum_ FROM department,
(SELECT deptname,SUM(num) AS sum_ FROM class
GROUP BY deptname
HAVING sum_ >= 30) temp
WHERE temp.deptname = department.deptname
INSERT INTO department VALUES(006,'物理');
START TRANSACTION-- -------------------------------------
UPDATE class --
SET num = num -1 --
WHERE classid = (SELECT classid FROM student --
WHERE `name` = '张三') --
DELETE FROM student --
WHERE `name` = '张三' --
COMMIT-- ------------------------------------------------
扫一扫
1314
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
