MySQL: 6、MySQL语句
1、MySQL服务在命令行中启动与关闭
启动:net start mysql
关闭:net stop mysql
2、MySQL命令行登入
mysql -u 用户名 -p 密码 :使用指定用户名和密码登录当前计算机中的mysql数据库
mysql -h 主机IP -u 用户名 -p 密码: 指定ip方式进行登录
3、SQL分类:
DDL(Date Definition Language) 数据定义语言:用来定义数据库对象:数据库、表、列
DML(Date Manipulation Language) 数据操作语言:用来对数据库中表的记录进行更新
DQL(Date Query Language) 数据查询语言:用来查询数据库中表的记录
DCL(Date Control Language) 数据控制语言:用来定义数据库的访问权限和安全级别及创建用户
4、DDL数据定义语言:操作数据库
1.创建数据库
create database 数据库名; -- 创建指定名称的数据库
create database 数据库名 character set 字符集; -- 创建指定名称的数据库,并且指定字符集(一般指定为utf8)
2.查看/选择数据库
use 数据库; -- 切换数据库
select database(); -- 查看当前正在使用的数据库
show databases; -- 查看Mysql 中都有那些数据库
show create database 数据库名; -- 查看一个数据库的定义信息
3.修改数据库字符集
alter database 数据库名 character set 字符集;
4.删除数据库
drop database 数据库名; -- 从MySQL中永久的删除某个数据库
5、DDL数据定义语言:操作表
1. 字段常用的数据库类型:
1)常用的数据类型:
create table 表名(
字段名1 字段类型(长度),
字段名2 字段类型 -- 注意最后一列不需要加逗号
)ENGINE=InnoDB DEFAULT CHARSET = utf8; -- 如果在定义数据库的时候定义了字符集这里是可以省略的
3.查看表:
show tables; -- 查看当前数据库中的所有表名
desc 表名; -- 查看数据库表的结构
show create table 表名; -- 查看表的字符集
4.删除表:
drop table 表名; -- 删除表(从数据库中永久删除某一张表)
drop table if exists 表名; -- 判断表是否存在,存在就删除,不存在就不删除
5.修改表:
rename table 旧表名 to 新表名; -- 修改表名
alter table 表名 character set 字符集; -- 修改表的字符集
alter table 表名 add 字段名 字段类型; -- 修改表,向表中添加新的字段
alter table 表名 modify 字段名 字段类型(长度); -- 修改表中的字段的数据类型或者长度
alter table 表名 change 旧字段名 新字段名 类型(长度); -- 修改字段名
alter table 表名 drop 字段名; -- 删除字段
6、DML数据操作语言: 操作表中的数据
1.插入数据
insert into 表名 (字段名1,字段名2...) values(字段值1,字段值2...);
insert into 表名 values(字段值1,字段值2...); -- 插入全部字段,不谢字段名
insert into 表名 (字段名) values(字段值); -- 插入指定的字段值
ps: varchar、char 、date 类型的值必须使用单引号或者双引号包裹
如果要插入空值,可以忽略不写或者插入null
如果插入指定字段的值,必须要写字段名
2. 更改数据
update 表名 set 列名 = 值; -- 不加条件会将所有列名都该为该值
update 表名 set 列名 = 值 where 条件表达式 : 字段名 = 值 ; -- 按条件删除列名
3.删除数据
delete from 表名; -- 删除所有数据
delete from 表名 where 字段名 = 值; -- 跟进条件删除数据
删除表中所有数据有两种方法:
delete from 表名; --》 有多少条记录就执行多少次删除操作,效率低
truncate table 表名; --》 先删除整张表,然后再重新创建一张一模一样的表,效率高
7、DQL数据查询语言: 查询表中的数据
1. 简单查询
select 列名 from 表名; -- 普通查询
select 列名 as 'XX' from 表名; -- 别名查询
select distinct 列名 from 表名; -- 去重查询
select ename,salary + 1000 from emp; -- 运算查询
2. 条件查询
select 列名 from 表名 where 条件表达式;
3. 条件查询用到的运算符:
1.比较运算符:
2. 逻辑运算符:
3. 模糊查询 :
8、DQL数据查询语言:操作表单
1、 聚合函数:
SELECT 聚合函数(字段名) FROM 表名;
常用的五个聚合函数:
count(字段): 统计指定列不为null的记录记录行数
sum(字段):计算指定列的数值和
max(字段):计算指定列的最大值
min(字段):计算指定列的最小值
avg(字段):计算指定列的平均值
ps: 统计表中的总记录数的多种方式:
select count(eid) from emp; -- 使用某个字段
select count(*) from emp; -- 使用 *
select count(1) from emp; -- 使用1 与 * 的效果相同
2、排序 : ORDER BY 关键字
单列排序 : 只按照某一个字段进行排序
SELECT 字段名 FROM 表名 [ WHERE 字段 = 值 ] ORDER BY 字段名 [ ASC/DESC];
组合排序:同时对多个字段进行排序,如果第一个字段相同就按照第二个字段进行排序
SELECT 字段名 FROM 表名 [ WHERE 字段 = 值 ] ORDER BY 字段名1 [ ASC/DESC],字段名2 [ ASC/DESC];
3、分组查询:GROUP BY 关键字
SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];
4、where 与 having的区别:
where 进行分组前的过来,where 后面不能写聚合函数
having进行分组后的过滤,having后面可以写聚合函数
5、limit关键字
limint是限制的意思,用于限制返回的查询结果的行数,limit语法是mysql的方言,用来完成分页
格式: SELECT 字段1,字段2... FROM 表名 LIMIT offset,length;
limint offset,length;关键字可以接受一个 或者 两个为0或者正整数的参数
offset 起始行数,从0开始计数,如果省略则默认为0
length 返回的行数
9、DCL数据控制语言:用来定义数据库的访问权限和安全级别,及创建用户
1、创建用户
格式: CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
案例:
1)创建 admin1 用户,只能在 localhost 这个服务器登录 mysql 服务器,密码为 123456
CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';
2) 创建 admin2 用户可以在任何电脑上登录 mysql 服务器,密码为 123456
CREATE USER 'admin2'@'%' IDENTIFIED BY '123456';
ps: % 表示 用户可以在任意电脑登录 mysql服务器.
2、用户授权
格式:GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
案例:
1) 给 admin1 用户分配对 db4 数据库中 products 表的 操作权限:查询
GRANT SELECT ON db4.products TO 'admin1'@'localhost';
2) 给 admin2 用户分配所有权限,对所有数据库的所有表
GRANT ALL ON *.* TO 'admin2'@'%';
3、查看权限
格式:
SHOW GRANTS FOR '用户名'@'主机名';
案例:
SHOW GRANTS FOR 'root'@'localhost';
4、删除用户:DROP USER '用户名'@'主机名';
-- 删除 admin1 用户
DROP USER 'admin1'@'localhost';
5、查询用户
选择名为 mysql的数据库, 直接查询 user表即可
SELECT * FROM USER;
10、SQL约束
1、常见的约束
主键 primary key 不可以重复、唯一、非空
唯一 unique 某一列不允许为空
非空 not null 不允许为空
外键 foreign key 设置外键
默认值约束 default 设置默认值
2、主键约束和唯一约束的区别:
主键约束唯一并且不能为空,唯一约束唯一但是可以为空
一个表中只能有一个主键,但是可以有多个唯一约束
3、主键自增
使用auto_increment 表示自动增长(字段类型必须是整数类型)
默认地auto_increment 的开始值是1,但是可以通过下面这种方式设置
方式1:
CREATE TABLE emp(
eid INT PRIMARY KEY
)AUTO_INCREMENT = 100 ;
方式2:
ALTER TABLE 表名 AUTO_INCREMENT = 100;
ps: 这里需要注意的是,如果一开始你设置了主键自增的值,如果在采用方式2的话,
方式2设置的主键自增的值必须比之前的大,否着无效
3.1 delete和truncate删除表中所有数据对自增长的影响
delete 只是删除表中所有的数据,对自增没有影响
truncate 是将整个表删除掉,然后创建一个新的表,自增的主键从1开始
11、数据库事务
1、MySQL两种方式进行事务提交操作:
- 自动提交事务
mysql 默认每条 DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务
语句执行完毕自动提交事务,mysql默认开启自动提交事务
- 手动提交事务
开启事务: start transaction 或者 begin
提交事务: commit
回滚事务: rollback
- 查看提交状态
show variables like 'autocommit';
- 修改提交状态
set @@autocommit = off;
on: 自动提交 off:手动提交
2、事务的四大特性: ACID
原子性: 每个事务都是一个整体,不可再拆分,事务中所有的sql语句要么都执行成功,要么都执行失败
一致性:事务在执行前数据库的状态与执行后数据库的状态要保持一直,如:转账前2个人总金额是2000,
那么转账后2个人总金额也是2000
隔离性: 事务与事务之间不应该相互影响,执行时保持隔离的状态
持久性: 一旦事务执行成功,对数据库的修改是持久的,就算计算机关闭,数据也是要保存下来的
3、数据并发访问会产生的问题
一个数据库有可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库,数据库的相同数据可能
被多个事务同时访问,如果不采取隔离措施,就会导致各种问题,破坏数据的完整性。
并发访问会产生的问题:
脏读:一个事务读取到另一个事务中尚未提交的数据
不可重复读:一个事务中两次读取的数据内容不一致
例如事务a读取一个数据2次, 此时事务b也访问该数据,并且在事务a两次读取数据之间进行了修改
导致事务a在多次读取到的数据不一样。这种就叫做不可重复读
幻读:幻读是指同一个事务内多次查询返回的结果集不一样,例如事务a对表中的数据进行修改,这种修改涉及到表
中的全部数据。同时,第二个事务也修改了这个表中的数据,这种修改是向表中插入一行新的数据,那么,
操作第一个事务的用户就会发现表中还有没有修改的数据行,就好像发生了幻觉一样,这就叫做幻读
不可重复读和幻读的区别:
不可重复读的重点是修改,同样的条件,你读取过的数据,再次读取出来发现值不一样
幻读的重点在于新增或者删除,同样的条件,第一次喝第二次读取出来的记录数不一样
4、事务的四种隔离级别
注意: serializable串行化可以彻底解决幻读,但是事务只能排队执行,严重影响效率,数据库不会使用这种隔离级别
查看隔离级别:
select @@tx_isolation;
设置事务隔离级别,需要退出mysql重新登录后才能看到隔离级别的变化
set global transaction isolation level 级别名称;
12、多表
1、创建外键约束
1)新建表时添加外键
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,
-- 添加外键约束
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
2) 已有表添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
3) 删除外键约束
alter table 从表 drop foreign key 外键约束名称;
4)添加数据库时,应该先添加主表中的数据,删除数据时,应该先删除从表中的数据
5) 级联删除
- 如果想实现删除主表数据的同时,也删除掉从表的数据,可以使用级联删除操作: ON DELETE CASCADE
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id),
ON DELETE CASCADE -- 添加级联删除
);
2、表与表之间的关系:
一对多关系:最常见的关系,例如班级对学生,部门对员工
多对多关系:学生对应课程,用户对应角色
一对一关系:使用较少,因为一对一关系可以合成为一张表
3、多表查询
1)内连接查询:通过指定的条件去匹配两张表中的数据,匹配上就显示,匹配不上就不显示
隐式内连接:form字据后面写多个表名,使用where指定连接条件的,这种方式就是隐式内连接
select 字段名 from 左表,右表 where 连接条件;
显示内连接:使用 inner join ... on 这种方式就是显式内连接
select 字段名 from 左表 [ inner ] join 右表 on 条件 [where 条件];
2)外链接查询:
左外连接:以左表为基准,匹配右表中的数据,如果匹配得上,就展示匹配到的数据
如果匹配不到,左表中的数据正常显示,右表的数据展示为null
select 字段名 from 左表 left [outer] join 右表 on 条件;
右外连接:以右表为基准,匹配坐标中的数据,如果能匹配到,就展示匹配到的数据
如果匹配不到,右表中的数据正常展示,左表的数据展示为null
select 字段名 from 右表 right [outer] join 右表 on 条件;
4、子查询
1、子查询分类
where 型子查询:将子查询的结果作为父查询的比较条件
select 查询字段 from 表 where 字段=(子查询);
from 型子查询 : 将子查询的结果作为一张表提供给父层查询使用,子查询的 结果作为一张表 时,
一定要起一个别名,否则无法访问表中的字段
select 查询字段 from (子查询)表别名 where 条件;
exists型子查询:子查询的结果是单列多行,类型于一个数组,父层查询使用 IN 函数,包含子查询的结果
select 查询字段 from 表 where 字段 in (子查询);
13、 索引