库操作
创建，修改，删除称之为定义
创建数据库
CREATE DATABASE 数据库名;
CREATE DATABASE db1 charset utf8mb4; #不要忘了设置字符集
查看数据库
SHOW DATABASES; #图形化管理工具里面可以直接看到有哪些数据库
SHOW create DATABASE db1; #查看创建数据库db1的代码
使用数据库
USE db1 ; #切换到某个文件夹，即数据库
select database(); #查看当前所在的文件夹，即数据库
修改字符集和排序规则
ALTER DATABASE oldguo CHARSET utf8mb4;
删除数据库
DROP DATABASE db1; drop database db1;
表操作
创建表
要指定字段和字段类型，要指定主键，最后一个字段后面没有逗号
CREATE TABLE stu (
ID int(11) AUTO_INCREMENT PRIMARY KEY,
name varchar(20) CHARACTER SET utf8 NOT NULL,
nicename varchar(20) CHARACTER SET utf8 DEFAULT NULL,
sex char(1) CHARACTER SET utf8 DEFAULT NULL,
in_time datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意事项：设置字符集
--- 建表规范 *****
--- 1. 表名小写字母,不能数字开头,
--- 2. 不能是保留字符,使用和业务有关的表名
--- 3. 选择合适的数据类型及长度
--- 4. 每个列设置 NOT NULL + DEFAULT .对于数据0填充,对于字符使用有效字符串填充
--- 5. 没个列设置注释
--- 6. 表必须设置存储引擎和字符集
--- 7. 主键列尽量是无关列数字列,最好是自增长
--- 8. enum类型不要保存数字,只能是字符串类型
修改表
ALTER TABLE 表名 ADD 新列名 数据类型 [完整性约束条件]; #增加列
ALTER TABLE 表名 ADD 新列名 数据类型 [完整性约束条件] first; #表示将该字段作为第一个字段
ALTER TABLE 表名 ADD 新列名 数据类型 [完整性约束条件] after 字段名; #表示将该字段放在哪个字段后面
alter table t1 modify name char(13); #将name字段的宽度改为13
alter table t1 change name Name char(10); #改字段名
ALTER TABLE `表名 Drop 列名 #删除列
ALTER TABLE `原表名 rename 新表名; #给表重命名
复制表
CREATE TABLE t1 select user,host from mysql.user; #将查询结果放到一个新表中
CREATE TABLE t3 select user,host from mysql.user where 1>3; 创建t3表,它复制了mysql.user的几个字段
CREATE TABLE t2 like mysql.user; #创建t2表，它复制了mysql.user的字段
select * from mysql.user\G; \G表示按行显示，mysql是系统自带的库，user为其下的一个表
select * from mysql.user\c \c表示取消，即不执行该语句
删除表
DROP TABLE stu #删除整个表
查看表
show create table 表名; #查看表的详细信息
desc 表名; #查看表结构，desc =describe
show tables; #查看当前文件夹下的所有表
记录
查询记录
SELECT ID,name, nicename FROM stu WHERE sex='男' ORDER BY IDDESC
DESC表示数字从大到小排列
ASC相反，默认从小到大
SELECT distinct 字段1，字段2，字段3.... FROM <表 1>, <表 2>…
[WHERE <条件表达式>] #WHERE 子语句作用于表
[GROUP BY
[ORDER BY
[LIMIT[
关键字的执行优先级
from where group by having select distinct order by limit
distinct针对查询结果的一整条记录
select distinct post from emp; #post字段为岗位名称
该语句表示：选择学号Sno，课程号Cno两列并按课程号Cno来分组，并计算选每一课程的学生人数
SELECT Cno,COUNT(Sno) #对学号Sno 列进行运算
FROM SC #SC为学生选课表，包括学号Sno，课程号Cno，成绩Grade三列
GROUP BY Cno
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3; #筛选出选修超过3门课程的学生
select name, salary2 two from emp; #字段中值的运算，salary2字段重命名为two
select concat(name,":",age) from emp; #concat连接作用
select concat_ws(":",name,age,sex) from emp; #concat_ws连接作用,第一个参数是分隔符
WHERE 条件表达式
确定范围（一般是数值的连续范围）
WHERE 列名 BETWEEN ... AND ...#从这一列中选出满足BETWEEN 条件的，含边界值
WHERE 列名 NOT BETWEEN ... AND ...
确定集合（）
WHERE 列名 IN( ) #从这一列中选出满足in条件的
WHERE 列名 NOT IN( )
查询空值
WHERE sex is NULL
WHERE sex is NOT NULL
字符匹配
%表示任意长度的字符串，_(下横线)表示任意单个字符
WHERE name LIKE '刘%' #查询姓刘的 WHERE name NOT LIKE '刘%' #查询不姓刘的
WHERE name LIKE '欧阳_' #查询姓欧阳且名字为三个字的
HERE name LIKE '_阳%' #查询第二个字为阳的
多条件查询
WHERE 条件1 AND/OR 条件2 AND/OR 条件3......
IN 实际上是多个OR运算符的缩写
like一般用于字符串类型的匹配
group by
select @@sql_mode; #查看sql模式
set global sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES";
select post,max(salary) from emp group by post;
select post,salary from emp group by post; #设置‘sql_mode="ONLY_FULL_GROUP_BY"是会报错，因为分组以后，只能查到分组字段和组内多条记录聚合的结果
没有group by关键字时，默认分为一组，聚合函数只能在分完组以后使用
select post,group_concat(name,age) from emp group by post;#结果如下
group_concat(name,age)
张野28
歪歪48,丫丫38,丁丁18,星星18,格格28
alex78,wupeiqi81,yuanhao73,liwenzhou28,jingliyang18,jinxin18,成龙48
egon18
having
select post,avg(salary) from emp group by post having avg(salary)>10000;
order by
select * from emp order by age asc,salary asc; #多重排序
limit
select * from emp limit 3; #显示3条记录
select * from emp limit 0,5; #从0开始不包括0，取5条
select * from emp limit 5,5;
正则表达式
select * from emp where name regexp "jin.*(n|g)$";
多表查询
select * from empl,depa; #两张表的笛卡儿积
连接查询:一个查询同时涉及两个以上的表
子查询：把一个查询的结果作为另一个查询的条件
连接查询比子查询效率高？具体情况具体分析
外连接 OUTER JOIN
1.左外连接
FROM Student LEFT JOIN SC ON(Student.Sno=SC.Sno)
select * from empl LEFT JOIN depa on empl.dep_id=depa.id;
查询保留在表Student中而不在表SC的记录，这些记录在表SC的字段的值为NULL
2.右外连接 RIGHT JOIN
select * from empl RIGHT JOIN depa on empl.dep_id=depa.id;
3.内连接 取两张表有对应关系的记录
select * from empl inner JOIN depa on empl.dep_id=depa.id;
select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1 inner join (select post, max(hire_date) from emp group by post) as t2 on t1.post=t2.post;
将一个查询结果用括号括起来，起个名，当成一个表来使用
select t1.* from emp as t1 inner join (select post, max(hire_date) as max_date from emp group by post) as t2 on t1.post=t2.post where t1.hire_date=t2.max_date;
1.5 关于多表连接语法规则
1.首先找涉及到的所有表
2.找到表和表之间的关联列
3.关联条件写在on后面
A join B on 关联列
-
所有需要查询的信息放在select后
-
其他的过滤条件where group by having order by limit 往最后放
6.注意：对多表连接中，驱动表选择数据行少的表。后续所有表的关联列尽量是主键或唯一键（表设计），至少建立一个索引。
增加记录
INSERT INTO db1.t1(name, nicename) VALUES ('张三4','三哥4'), ('张三5','三哥5'), ('张三6','三哥6')
db1.t1 =数据库名.表名
CREATE TABLE user1 select user,host from mysql.user where 1>3;
INSERT INTO user1 select user,host from mysql.user;
修改记录
UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
UPDATE stu SET sex='男' WHERE sex is NULL
truncate t8; #清空表,物理删除，立即释放存储空间
删除记录
DELETE FROM stu WHERE Sno='123456'
逻辑删除，不会立即释放存储空间
show介绍
show databases; 查看数据库名
show tables; 查看表名
show create database xx; 查看建库语句
show create table xx; 查看建表语句
show processlist; 查看所有用户连接情况
show charset; 查看支持的字符集
show collation; 查看所有支持的校对规则
show grants for xx; 查看用户的权限信息
show variables like '%xx%' 查看参数信息
show engines; 查看所有支持的存储引擎类型
show index from xxx 查看表的索引信息
show engine innodb status\G 查看innoDB引擎详细状态信息
show binary logs 查看二进制日志的列表信息
show binlog events in '' 查看二进制日志的事件信息
show master status ; 查看mysql当前使用二进制日志信息
show slave status\G 查看从库状态信息
show relaylog events in '' 查看中继日志的事件信息
show status like '' 查看数据库整体状态信息
浙公网安备 33010602011771号