MySQL常用命令介绍_mysql -e source

1、创建数据库

CREATE DATABASE test;
CREATE DATABASE 'create'; # 用关键字创建数据库要使用单引号''

1
2
3

2、删除数据库

DROP DATABASE test;
1

3、备份数据库和数据库中表

#备份数据库
mysqldump -u root -p -B 数据库1 数据库2 > bak.sql #这个命令要在dos命令下执行
#备份数据库中表
mysqldump -u root -p  数据库1 表1 表2 > bak1.sql #这个命令要在dos命令下执行

1
2
3
4
5

4、恢复数据库

source bak.sql #要在MySQL命令行下执行
1

5、创建表

CREATE TABLE table_name(
	field1 字段类型,
    field2 字段类型,
    field3 字段类型 unsigned,
)CHARACTER SET utf8 COLLATE utf8_bin;
# 创建无符号字段 添加unsigned 关键字（大于等于0）

# 日期举例
CREATE TABLE employ (
  birthday DATE,
  job_name DATETIME,
  login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
); # login_time 自动更新时间 

INSERT INTO employ (birthday, job_name)
VALUES
  (
    '2020-03-23',
    '2020-03-21 12:34:23'
  )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

6、修改表

①、添加列

ALTER table table_name add 字段名 字段类型 after 字段名
#after 字段名 表示在该字段之后添加
ALTER TABLE employ ADD (age INT);
1
2
3

②、修改列

ALTER TABLE table_name modify 字段名 字段类型


ALTER TABLE employ MODIFY age VARCHAR(2)
1
2
3
4

③、删除列

ALTER TABLE table_name drop 字段名

ALTER TABLE employ DROP age
1
2
3

④、修改表名

RENAME TABLE table_name to new_table_name;

RENAME TABLE employ TO emp
1
2
3

⑤、修改表字符集

ALTER TABLE table_name charset utf8;

ALTER TABLE emp CHARSET utf8
1
2
3

⑥、修改表中列名

ALTER TABLE table_name change 原字段名 新字段名 字段类型

ALTER TABLE emp CHANGE age age1 INT
1
2
3

7、查看表中所有字段属性

DESC table_name

DESC emp
1
2
3

8、添加数据

insert into table(字段1,字段2) values(value1,value2)

INSERT INTO emp(birthday,job_name,age1) VALUES('2001-01-23','2020-09-23 12:34:23',12)
# 注意细节：
-- 1、插入的数据应与字段的数据类型相同。
-- 2、数据的长度应在列的规定范围内 
-- 3、在values中列出的数据位置必须与被加入的列的排列位置对应
-- 4、字符和日期型数据应包含在单引号中。
-- 5、列可以插入空值（前提是该字段允许为空）
-- 6、insert into table_name(列名..) values (),(),() 可以添加多条数据
-- 7、如果是给表中的所有字段添加数据，可以不写前面的字段名称
-- 8、默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错。
	 -- 如果某个列没有指定not null,那么再添加数据时没有给定值，则默认为null
	 -- 如果希望指定默认值，则在创建字段给定一个默认值
CREATE TABLE employ (
  birthday DATE,
  job_name DATETIME,
  price double NOT NULL  default 3000 #指定默认值
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

9、修改数据

update table_name set 字段名= 字段端 where 字段= id
# 注意细节
-- 如果要同时修改多个列，则可以在字段间加，
update emp set filed1=value1,field2= value2 where field3 = value3
-- 如果要根据查询的数据进行数据更新，可以使用以下方式
UPDATE blog_article a,(SELECT id MOD 4 i,id tid FROM blog_article ) temp  SET show_index= 1 WHERE temp.tid=a.id AND temp.i=0
1
2
3
4
5
6

10、删除数据

delete from table_name where 字段名=字段值
1

11、删除表

drop table table_name
1

12、查询数据

1）、单表查询

• where 字句使用

SELECT [DISTINCT] * |(字段1,字段2,字段3..) from table_name
-- DISTINCT 查询的数据是否去除重复数据
-- * 代表查询所有数据

-- 查询语句中可以使用表达式
SELECT [DISTINCT] * |(字段1,表达式,字段3..) from table_name
-- 查询列名可以使用as 指定别名
SELECT [DISTINCT] * |(字段1 as 别名,表达式 as 别名2 ,字段3..) from table_name
-- 可以通过where 字段添加查询条件
SELECT [DISTINCT] * |(字段1,表达式,字段3..) from table_name where 字段 (=|>|<) and 字段 like 'xxx%'
-- like 表示模糊查询 表示只要匹配上就行 不管后面有多少字符
1
2
3
4
5
6
7
8
9
10
11

• order by 子句

select * from table_name order by 字段名 #默认升序

select * from table_name order by 字段名 desc #降序
-- 同时使用where和order by，order by放在where 后面
select * from table_name where 字段=值 order by 字段名 
1
2
3
4
5

• 使用统计函数

# count 函数
select count(*)|count(列) from table_name
-- count(*) 返回满足条件的记录的行数
-- count(列) 统计满足条件的某列有多少个，会排除null

# sum 函数 计算表中某列的总和 ，仅对数值起作用
select sum(字段) from table_name

# avg函数  计算某列的平均值
select avg(字段) from table_name

# max/min函数 计算某列的最大值/最小值
select max(字段)|min(字段) from table_name
1
2
3
4
5
6
7
8
9
10
11
12
13

• 使用group by /having字句

# 按照分组查询
select column1,column2,colum3 from table_name group by coloum1 having 表达式
-- having在group by查询的基础上通过表达式过滤
1
2
3

• 字符串相关函数

select charset(字段) from table_name;
select concat(字段1,字段2,字段3) from table_name;
select lcase(字段) from table_name
select left(字段,长度) from table_name
select length(字段) from  table_name
select replace(字段,'旧值','替换的新值') from table_name
select substring(字段,起始位置,字符数) from table_name
select ltrim/rtrim/trim(字段) from table_name
1
2
3
4
5
6
7
8

• 数学相关的函数

# abs(num) 绝对值
SELECT ABS(-1) FROM DUAL
# bin(decimal_num)10进制转二进制
SELECT BIN(8) FROM DUAL # 
# ceiling(num) 向上取整，得到比num大的最小整数
SELECT CEILING(1.1) FROM DUAL
# conv(num) 进制转换
# 下面的含义是8  是十进制的8,转成2进制输出
select conv(8,10,2) from table_name
# 下面的含义是16 是16进制的16，转成10进制输出
select conv(16,16,10) from table_name
# floor(num) 向下取整，得到比num小的的最大整数
SELECT FLOOR(2.4) FROM DUAL
# FOMART(num,2) 四舍五入保留两位小数
SELECT FORMAT(2.3456,2) FROM DUAL
# least(num1,num2,num3) 求最小值
SELECT LEAST(-1,3,0,10) FROM DUAL
# mod(num,3) 求余
SELECT MOD(10,3) FROM DUAL
# rand([seed]) 返回随机数
-- 如果使用rand() 每次返回不谈的随机数
SELECT RAND() FROM DUAL
-- 如果使用rand(seed) 返回随机数，范围为 0=<v<=1 ,随机数保持不变
SELECT RAND(3) FROM DUAL #每次返回固定的随机数 0.9057697559760601
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

• 日期相关函数

SELECT CURRENT_DATE() FROM DUAL #查询当前的日期
SELECT CURRENT_TIME() FROM DUAL #查询当前时间 23:06:11
SELECT CURRENT_TIMESTAMP() FROM DUAL # 查询当前日期和时间 2021-10-26 23:07:32
SELECT DATEDIFF( DATE_ADD('1987-2-12', INTERVAL 80 YEAR),NOW())/365  FROM DUAL
1
2
3
4

SELECT DATE('2020-02-12 12:34:56') FROM DUAL # 返回指定时间的日期部分
SELECT DATE(NOW()) FROM DUAL # 查询当前时间的日期部分
SELECT DATE_ADD('1987-2-12', INTERVAL 80 YEAR) FROM DUAL # 在指定的日期后加上特定的日期或时间
-- 指定的日期可以是年月日 或者年月日时分秒 
-- 特定的日期单位可以是年(year) 月(month) 日(day) 时（hour) 分（minute) 秒（second)
SELECT DATE_SUB('1987-2-12', INTERVAL 80 SECOND) FROM DUAL # 在指定的日期后减去特定的日期或时间
SELECT DATEDIFF( DATE_ADD('1987-2-12', INTERVAL 80 YEAR),NOW())/365  FROM DUAL #计算两个日志之差
1
2
3
4
5
6
7

# YEAR|MONTH|DAY 获取年|月|日
SELECT YEAR(NOW()) FROM DUAL
SELECT UNIX_TIMESTAMP() FROM DUAL # 返回从1970-1-1到现在的秒数 1635262913
SELECT FROM_UNIXTIME(1635262913,'%Y-%m-%d') FROM DUAL # 将时间戳（单位秒）转换成指定格式的数据 
-- %Y-%m-%d %H:%i:%s 格式固定
1
2
3
4
5

• 加密函数和系统函数

# user() 查询当前登录的用户 
SELECT USER() FROM emp # root@localhost
# database() 查询当前使用的数据库
SELECT DATABASE() #wxtest
# md5(str) 为字符串生成32位的字符
SELECT MD5('assdf') FROM DUAL
#password(str) 加密函数
1
2
3
4
5
6
7

• 流程控制函数

# 如果表达式返回true,则返回第一个结果，否则返回第二个结果
SELECT IF(3>0,'对','错') AS judge FROM DUAL # 返回对

# 如果表达式1不为null，则返回表达式1，否则返回表达式2 
SELECT IFNULL(3>2,'ok') FROM DUAL # 返回1
SELECT IFNULL(2>3,'ok') FROM DUAL # 返回0
SELECT IFNULL(NULL,'ok') FROM DUAL #返回ok
# 如果表达式1为true 则返回 第一个结果，如果表达式2位true,则返回第二个结果，否则返回最后一个结果
SELECT CASE WHEN TRUE THEN "jack"
		WHEN TRUE THEN 'mary'
		ELSE 'tom' END  # 返回jack
		
#判断某个字段是否为null 用is
1
2
3
4
5
6
7
8
9
10
11
12
13

• 增强查询

# 使用模糊查询占位使用_
SELECT * FROM table_name where name like '__S%' #查询第三个字符为大写的S的值
# 分页查询  start 表示起始位置 ，size表示显示的个数 第一页从下标0 开始
SELECT * FROM table_name order by 字段1 limit  start,size

SELECT * FROM grade ORDER BY id LIMIT 3,5

# group by having order by limit 一起组合使用时，顺序是
-- group by - having - order by - limit
1
2
3
4
5
6
7
8
9

• 多表查询

#给表起别名 格式是 表名 表别名
select * from table_name table_name_alias
# 多表查询 查询的条件是表的个数减1 ，否则会出现笛卡尔集
# 自查询 将用一张表当成两张表来使用 ，需要给表起别名
SELECT  grade1.id ,grade2.score 
	FROM grade grade1 ,grade grade2
	WHERE grade1.id= grade2.score

1
2
3
4
5
6
7
8

• 子查询

# 可以将查询的结果作为另一个查询语句的查询条件，即为子查询
SELECT * FROM grade 
		WHERE id=(SELECT id FROM grade WHERE score=10)

# 把子查询当成一张临时表 和原表进行关联查询
SELECT id,`name`,goods_info.`typeId`,price FROM 
	(SELECT typeId,MAX(price) AS max_price
	FROM goods_info GROUP BY typeId ) temp_goods,goods_info 
	WHERE temp_goods.max_price= goods_info.`price`
-- 将 SELECT typeId,MAX(price) AS max_price FROM goods_info GROUP BY typeId 查询的结果作为一个临时表与原表进行关联查询，查询的字段如果两张表都有的话需要写清楚查询的哪张表的哪个字段 比如上面的 goods_info.`typeId`格式为：（表名.字段名）

#all/any操作符 所有/任意一个条件满足返回结果
-- -------------all------------------
SELECT * FROM goods_info
	WHERE price >ALL(
		SELECT price FROM goods_info WHERE typeId=2)
-- 上面的语句可以使用下面的方式
SELECT * FROM goods_info
	WHERE price >(
		SELECT MAX(price) FROM goods_info WHERE typeId=2)
-- -------------any------------------
SELECT * FROM goods_info
	WHERE price >ANY(
		SELECT price FROM goods_info WHERE typeId=1)
-- 同样上面的语句可以写成下面这种方式
SELECT * FROM goods_info
	WHERE price >(
		SELECT MIN(price) FROM goods_info WHERE typeId=1)
		
# 多列子查询 查询条件使用多列 
SELECT * FROM goods_info WHERE (price,typeId)=(
                    SELECT price,typeId FROM goods_info WHERE `name`='生菜')
-- 查询条件的字段必须和后面查询语句的字段保持一致，比如price,typeId 顺序不能错，否则就查不出结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

13、蠕虫复制

# 将原有表的数据快速大量的复制到目标表中
CREATE TABLE my_tab(id INT,`name` VARCHAR(32),description VARCHAR(50),price DOUBLE)
-- 快速复制数据到新创建的表中
INSERT INTO my_tab(id,`name`,description,price)
	SELECT id,`name`,description,price FROM goods_info
-- 快速创建结构相似的表 用like关键字
CREATE TABLE my_tab1 LIKE my_tab
-- 快速插入数据（结构相似的表可以快速直接插入数据）
INSERT INTO my_tab1 SELECT * FROM my_tab
1
2
3
4
5
6
7
8
9

14、合并查询

# UNION all /UNION  将查询结果进行合并，union all不去重 ，union 去重
-- ------------UNION ALL----------------
SELECT * FROM goods_info WHERE price>300 
UNION ALL SELECT * FROM goods_info WHERE typeId=2
-- ------------UNION-------------------
SELECT * FROM goods_info WHERE price>300 
UNION  SELECT * FROM goods_info WHERE typeId=2
1
2
3
4
5
6
7

15、外连接

• 左外连接

# 显示所有表1的数据，如果表1中有的数据表2中没有，则以null填充，表2中多的数据会被舍弃
select ... from 表1 left join 表2 on 表1.key= 表2.key
1
2

• 右外连接

# 显示所有表2的数据，如果表2中有的数据表1中没有，则以null填充，表1中多的数据会被舍弃
select ... from 表1 right join 表2 on 表1.key= 表2.key
1
2

16、主键

# primary key 主键
# UNIQUE 表示该列不能重复

CREATE TABLE t01(id INT PRIMARY KEY,`name` VARCHAR(32))
-- 指定id为主键后，添加数据时 不能添加id相同的数据，同时主键不能为null
INSERT INTO t01 VALUES(1,'tom') #√
INSERT INTO t01 VALUES(1,'jack') #×
INSERT INTO t01 VALUES(NULL,'jack') #×
-- 创建主键还有下面这种方式 ----
CREATE TABLE t02(
	id INT ,
	`name` VARCHAR(32),
	email VARCHAR(32),PRIMARY KEY(id))
-- -------------------------联合主键--------------------
CREATE TABLE t02(
	id INT ,
	`name` VARCHAR(32),
	email VARCHAR(32),PRIMARY KEY(id,`name`))# 将两个键或者多个键联合起来 当成一个主键 

-- --------------------------UNIQUE--------------------
CREATE TABLE t03(id INT UNIQUE ,`name` VARCHAR(32))
INSERT INTO t03 VALUES(1,'aaa') #√
INSERT INTO t03 VALUES(1,'bbb') #×
INSERT INTO t03 VALUES(NULL,'bbb') #√
-- 如果没有指定not null 则可以重复添加null
INSERT INTO t03 VALUES(NULL,'bbb') #√
-- 如果指定了not null，则不能添加null
INSERT INTO t03 VALUES(NULL,'bbb') #×
-- 一张表中可以有多个unique 
CREATE TABLE t03(id INT UNIQUE NOT NULL ,`name` VARCHAR(32) UNIQUE)
DROP TABLE t03
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

17、外键

CREATE TABLE my_class(id INT PRIMARY KEY,`name` VARCHAR(32))
INSERT INTO my_class VALUES(100,'java'),(200,'web')
-- -----------------创建外键----------------
CREATE TABLE my_stu(id INT PRIMARY KEY,`name` VARCHAR(32),
		class_id INT,
		FOREIGN KEY(class_id) REFERENCES my_class(id))
INSERT INTO my_stu VALUES(1,'jack',100) #添加√
INSERT INTO my_stu VALUES(2,'jack',200) #添加√
INSERT INTO my_stu VALUES(2,'jack',300) #添加× 因为主表中没有id为300的数据
INSERT INTO my_stu VALUES(3,'jack',NULL) 
-- 表创建好之后可以通过以下命令创建外键
ALTER TABLE blog_article ADD CONSTRAINT  FOREIGN KEY(category) REFERENCES blog_cate(id)
-- 约束的键没有指定not null ,可以添加null
DELETE FROM my_class WHERE id=100 # 删除失败 因为有约束键关联该主键，所以数据无法删除


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

练习题

# 创建goods 表
CREATE TABLE goods (goods_id INT PRIMARY KEY,
		goods_name VARCHAR(32) NOT NULL DEFAULT '',
		unitprice DOUBLE CHECK( unitprice >1 AND unitprice <9999.99),
		category VARCHAR(32) NOT NULL DEFAULT '',
		provider VARCHAR(32) NOT NULL DEFAULT '')
#创建	customer表	
CREATE TABLE customer (customer_id INT PRIMARY KEY,
			`name` VARCHAR(32) NOT NULL DEFAULT '',
			address VARCHAR(32) NOT NULL DEFAULT '',
			email VARCHAR(32) UNIQUE NOT NULL,
			sex ENUM('男','女') NOT NULL,
			car_id CHAR(18) NOT NULL DEFAULT '')
#创建purchase表
CREATE TABLE purchase(order_id INT PRIMARY KEY,
			customer_id INT,
			goods_id INT,
			nums INT,
			FOREIGN KEY(customer_id) REFERENCES customer(customer_id),
			FOREIGN KEY(goods_id) REFERENCES goods(goods_id))

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

18、自增长

# AUTO_INCREMENT

CREATE TABLE t01( id INT PRIMARY KEY AUTO_INCREMENT,
		`name` VARCHAR(32))
# 自增长添加数据的两种方式
INSERT INTO t01 VALUES(NULL,'jack')
INSERT INTO t01(`name`) VALUES('tom')
1
2
3
4
5
6
7

19、创建索引 优化查询速度

create index 索引名 on table_name (字段) 
-- 创建索引后表会变大 ，会显著提高查询速度，主要用在海量表中
CREATE INDEX id_index ON t01(id)
1
2
3

20、事务

-- 创建事务演示
CREATE TABLE t02(id INT PRIMARY KEY,
		`name` VARCHAR(32))

-- 开启事务
START TRANSACTION
-- 创建回滚点
SAVEPOINT a

INSERT INTO t02 VALUES(1,'tom')
-- 创建第二个回滚点
SAVEPOINT b

INSERT INTO t02 VALUES(2,'jack')

-- 开始回滚到第二个回滚点
ROLLBACK TO b

-- 直接回滚到起点
ROLLBACK


SELECT * FROM t02
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

21、隔离级别

新版mysql查询事务隔离级别的的命令已经修改了，命令如下：

SELECT @@transaction_isolation
1

客户端A

客户端B

客户端C

22、MySQL引擎

SHOW ENGINES; #查看所有可用引擎
1

22、视图view

-- 创建视图
CREATE VIEW view01 AS SELECT id,username FROM `user`
-- 查询视图
SELECT * FROM view01
-- 查看创建的视图
SHOW CREATE VIEW view01
-- 删除视图
DROP  VIEW view01
-- 视图的修改会影响到主表的数据，同样主表的修改同样会影响视图
-- 修改视图
UPDATE view01 SET `username`='test' WHERE id=7

1
2
3
4
5
6
7
8
9
10
11
12

修改视图后主表数据同样跟着修改了

23、MySQL管理

-- 创建新用户
-- 'wl'@'localhost' 用户名和允许登录的位置
-- '123456' 登录密码 存储在数据库中是password()加密后的函数
CREATE USER 'wl'@'localhost' IDENTIFIED BY '123456'
-- 删除用户
DROP USER 'wl'@'localhost'


-- 修改密码
-- 给自己修改密码 可以直接修改，不需要权限
SET PASSWORD =PASSWORD('test')
-- 给其他用户修改密码，需要有修改密码的权限 否则会报权限错误
SET PASSWORD FOR 'wl'@'localhost' = PASSWORD('testtt')

-- 新版本MySQL 修改密码 使用 上面两条语句会报语法错误，可以使用下面的语句进行密码修改，同样也是需要修改密码的权限
ALTER USER 'wl'@'localhost' IDENTIFIED BY  '123';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

24、MySql用户权限管理

CREATE USER jack
SELECT `host`,`user` FROM mysql.`user`

CREATE USER 'tom'@'192.168.0.%' 
DROP USER jack
DROP USER 'tom'@'192.168.0.%'
1
2
3
4
5
6