数据库基本操作

系统数据库

information_schema(虚拟库)
用户表信息、列信息、权限信息、字符信息等

//查询都多少个库
mysql>  SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA;

show databases;

// 查询mysql库中有多少个表
mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mysql';

use mysql
show tables;

performance_schema       //主要存储数据库服务器的性能参数
mysql(授权库)                  //主要存储系统⽤户的权限信息
sys(优化库)                     //主要存储数据库服务器的性能参数

//使用socket链接
[root@i-m60lx3hh ~]#  mysql -S /tmp/mysql.sock -uroot -p'Wazx@123'

//如果找不到⽂件可以通过tcp连接进来然后通过如下命令查找
show global variables like 'socket';

# 数据库状态
status; #简写 \s

# 展示当前连接
mysql> show processlist;

#创建⼀个具有 root 权限的⽤户,并授予该⽤户远程连接权限。
CREATE USER 'root'@'%' IDENTIFIED BY 'Wazx@123';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

#重新加载权限表，使更改⽣效：
FLUSH PRIVILEGES;

# SQL语言分类
1.DDL(数据库定义语⾔ //开发⼈员)
数据库、表、视图、索引、存储过程、函数、CREATE DROP ALTER

2.DML(数据库操作语⾔ //开发⼈员)
插⼊数据 INSERT、删除数据 DELETE、更新数据UPDATE

3.DQL(数据库查询语⾔ //运维、开发⼈员)
查询数据 SELECT

4.DCL(数据库控制语⾔ //运维⼈员)
控制⽤户的访问权限 GRANT、REVOKE

//查看数据库
mysql> show databases;

//创建数据库
mysql> CREATE DATABASE test;

//使用数据库
mysql> use test;

//创建表
mysql> create table stu( id int(10), name varchar(20), age int(10), primary key(id));
# 每⼀张表都需要包含⼀个主键，主键唯⼀标识⼀条记录，唯⼀的字段，不可重复不能为空，通过`primary key`关
键字来定义。

//查看创建语句
mysql> show create table stu;

# 新加⼀个字段
alter table stu add column gender varchar(20);

# 修改⼀个字段
alter table stu modify column gender varchar(40);

# 删除⼀个字段
alter table stu drop column gender;

# 删除表
drop table stu;

# 查看当前数据库中的表
show tables;

# 向表中插⼊数据
insert into stu (id,name,age) values(1,'xu',28);

# 插⼊全部字段时可以只写表名
insert into stu values(2,'nss',29);

# 查看刚才添加的数据,"*"代表查询全部字段
select * from stu;

# 如果只想查询两个字段，则只写要查询的字段名
select name, age from stu;

# 也可以根据某个条件进⾏查询，⽐如只查询id为1的记录
select name age from stu where id=1;

# 更新语句
update stu set age=29 where id=1;

# 删除表中的数据
delete from stu where id=1;

存储性别、省份、类型等分类信息时选择TINYINT、char(1)或者ENUM
BIGINT存储空间更⼤，INT和BIGINT之间通常选择BIGINT
交易等⾼精度数据选择使⽤DECIMAL

//查看数据库版本
mysql> select version();

\h ：显示帮助信息，包括所有可⽤的 MySQL 命令和特殊命令
\c ：清除当前输⼊的命令
\q ：退出 MySQL 命令⾏⼯具。
\G ：将查询结果按列格式化，并以每⾏⼀个字段的⽅式显示。
\t：将查询结果转换为纯⽂本格式。
\n：将查询结果转换为纯⽂本格式，并在每⾏末尾添加换⾏符。
\d：设置语句分隔符

//查询表结构
desc mysql.slow_log;

//创建表
mysql> create table t1(id int,
    -> name varchar(10),
    -> sex enum('man','gril'),
    -> age int);

//删除表
mysql> drop table stu;

//查询表结构
desc t1;

#1.插⼊完整数据, 顺序插⼊: INSERT INTO 表名(字段1,字段2,字段n) VALUES (值1,值2,值 n);
mysql> insert into t1(id,name,sex,age) values ("1","xu","man","18");
Query OK, 1 row affected (0.01 sec)

#2.插⼊完整数据, 推荐⽅式 INSERT INTO 表名 VALUES (值1,值2,值n);
mysql> insert into t1 values("2","xu1","gril","10");
Query OK, 1 row affected (0.01 sec)

#3.指定字段插⼊, INSERT INTO 表名(字段2,字段3…) VALUES (值 2,值3…);
mysql> insert into t1(name,sex,age) values ("wing2","man","20");
Query OK, 1 row affected (0.00 sec)

#4.插⼊多条记录, INSERT INTO 表名 VALUES (值1,值2,值n),(值1,值2,值n);
mysql> insert into t1 values
("3","xu3","man","18"),
("4","xu4","man","18"),
("5","xu5","man","18");
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

# 4.更新数据 UPDATE 语句
//语法: 更新 表 设置 字段1=值1, 字段2=值2, WHERE 条件;
mysql> update t1 set name="xu1" where name="wing2";

// 修改密码示例, 查看表字段内容
mysql> select user,host,authentication_string from mysql.user;
mysql> update mysql.user set
authentication_string=password("Wazx@123")
where user='root' and host='localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//改完密码后需要刷新生效
mysql> flush privileges;

//删除字段
mysql> delete from t1 where name='xu1';

//清空表数据
mysql> truncate t1;

// 查询指定字段
mysql> select name,job,dep_id from t2;

//避免重复查询字段distinct
mysql> select distinct post from t2;

//通过四则运算查询, 计算每个⼈的年薪
mysql> select name,salary,salary*14 from t2;

//计算年薪并定义输出字段信息别名, AS可去掉
mysql> select name,salary,salary*14 AS Annual_salary from t2;

//定义显示格式 CONCAT() 函数⽤于连接字符串
mysql>  select concat(name,' annual salary:',salary*14) from t2;

# 条件查询
//单条件查询
mysql> select name,job from t2 where post='hr';

//多条件查询
mysql> select name,post,salary from t2 where post='hr' and salary >5000;

//查找薪资范围在8000-2000,使⽤BETWEEN区间
mysql> select name,salary from t2 where salary between 8000 and 20000;

//查找部⻔为Null, 没有部⻔的员⼯
mysql> select name,job from t2 where job is null;

//查找有部⻔的员⼯
 mysql> select name,job from t2 where job is not null;

//查看部⻔为空的员⼯
mysql> select name,job from t2 where job='';

//集合查询 
mysql> select name,salary from t2 where salary=4000 OR salary=5000 OR salary=8000;
mysql> select name,salary from t2 where salary in(4000,5000,8000);

//模糊查询like, 通配符%
mysql> select * from t2 where name like 'al%';

//通配符_ 匹配一个字符
mysql> select * from t2 where name like 'al_';

//按单列排序, 按薪⽔从低到⾼排序, 默认ASC(升序)      DESC（降序）
mysql> select * from t2 ORDER BY salary ASC;     

//多列排序, 先按⼊职时间，再按薪⽔排序
mysql> select * from t2 ORDER BY time DESC, salary ASC;

//限制查询数量,通过limit限制
mysql> select * from t2 limit 3;

//查询薪资最⾼前5名同事, 默认初始位置为0
mysql> select * from t2 ORDER BY salary DESC limit 5;

//从第三条数据开始查两条（显示4，5两条数据）
mysql> select * from t2 limit 3,2;

//从第4条开始, 并显示5条数据
mysql> select * from t2 ORDER BY salary DESC limit 3,5;

SELECT column1, column2, ...
FROM table_name
LIMIT [offset,] row_count;

其中， column1, column2, ... 是要查询的列名， table_name 是要查询的表名。 offset 是可选的，表示从第
⼏条记录开始返回，默认为0，即从第⼀条记录开始返回。 row_count 表示要返回的记录数。

使⽤LIMIT⼦句时，可以只指定 row_count ，这样会从第⼀条记录开始返回指定的记录数。也可以同时指定
offset 和 row_count ，这样会从指定的记录开始返回指定的记录数。

# 使用集合函数查询
//统计当前表总共多少条数据
mysql> select count(*) from t2;

//统计dep_id为101有多少条数据
mysql> select count(*) from t2 where dep_id=101;

//薪⽔最⾼
mysql> select MAX(salary) from t2;

//薪水最低
mysql> select min(salary) from t2;

//平均薪⽔
mysql> select avg(salary) from t2;

//总共发放多少薪⽔
mysql> select sum(salary) from t2;

//hr部⻔发放多少薪⽔
 mysql> select sum(salary) from t2 where post='hr';

//哪个部⻔哪个⼈薪⽔最⾼
mysql> select * from t2 where salary=(select max(salary) from t2);

# 分组查询

// GROUP BY 和 GROUP_CONCAT()函数⼀起使⽤
mysql> select post,GROUP_CONCAT(name) from t2 GROUP BY post;

mysql> select post,GROUP_CONCAT(name) AS Group_Post from t2 GROUP BY post;

#GROUP BY 和集合函数⼀起使⽤
mysql> select post,sum(salary) from t2 GROUP BY post;

# 正则表达式
mysql> select * from t2 where name REGEXP '^ali';     //以ali开头的

mysql> select * from t2 where name REGEXP 'm$';     // 以m结尾

# 多表查询
//交叉连接, 不使⽤任何匹配条件
mysql>  select t3.name,t3.age,t3.dep_id,t4.dept_name from t3,t4;

//内连接, 只连接匹配的⾏
 select t3.id,t3.name,t3.age,t4.dep_id,t4.dept_name from t3,t4
where t3.dep_id=t4.dep_id;

//外连接
SELECT 字段列表 FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

LEFT JOIN从左表(play_list)返回所有的⾏，即使在右表中(dept_name)中没有匹配的⾏。
与LEFT JOIN相对应的有RIGHT JOIN关键字，会从右表那⾥返回所有的⾏，即使在左表中没有匹配的
⾏。

# 左链接
mysql> select id,name,t4.dept_name from t3 left join t4 on t3.dep_id = t4.dep_id;

# 右链接
 select id,name,t4.dept_name from t3 right join t4 on t3.dep_id = t4.dep_id;

//符合条件连接查询
#1.以内连接的⽅式查询 t3和t4表, 找出公司所有部⻔中年龄⼤于25岁的员⼯
mysql> select t3.id,t3.name,t3.age,t4.dept_name
from t3,t4
where t3.dep_id = t4.dep_id
and age >25;

#以内连接的⽅式查询 t3和t4表，并且以age字段降序显示
mysql> select t3.id,t3.name,t3.age,t4.dept_name
from t3,t4
where t3.dep_id = t4.dep_id
ORDER BY age DESC;

# 子查询
⼦查询是将⼀个查询语句嵌套在另⼀个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件。
⼦查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS 等关键字 还可以包含⽐较运算符：=
、 !=、> 、<等

#带 IN 关键字的⼦查询 查询t3表，但dept_id必须在t4表中出现过
mysql> select * from t3 where dep_id IN (select dep_id from t4);

#代表运算符⼦查询, 查询年龄⼤于等于 25 岁员⼯所在部⻔（查询⽼龄化的部⻔）
mysql> select dep_id,dept_name from t4
 where dep_id IN
 (select DISTINCT dep_id from t3 where age >=25);

#⼦查询 EXISTS 关字键字表示存在。在使⽤ EXISTS 关键字时，内层查询语句不返回查询的记录，⽽是返回⼀个真
假值。
#Ture 或 False，当返回 Ture 时，外层查询语句将进⾏查询；当返回值为 False 时，外层查询语 句不进⾏查询

#t4 表中存在 dep_id=203，Ture
mysql> select * from t3
where EXISTS (select * from t4 where dep_id=203);

mysql> select * from t3 where EXISTS (select * from t4 where dep_id=300);
Empty set (0.00 sec)