库操作命令：
新建库：create database db1;
create database db2 default charset utf8;
查看库：show databases;
查看库信息：show create database db1;
删除库：drop database db1;
进入库：use db1;
创建用户：grant all privileges on db1.* to db@'192.168.1.%' identified by '123.com';
表操作命令：
查看表：show tables;
创建表：create table t1(id int,name char(10));
create table t2(id int,name char(10)) engine=innodb default charset=utf8;
########################创建表时列信息设置#########################
create table t2(
#可在数据类型后面加auto_increment（自增） primary key（主键，作用：约束值不能为空；加速查找）
#可在数据类型后面加not null不允许为空（默认允许为空）；
#可在数据类型后面加default *设置默认值
id int not null auto_increment primary key,
name char(10) not null，
sex char(2) default '男'
) engine=innodb default charset=utf8;
#################################################################
查看表内容：select * from t1;
表里插入数据：insert into t1(id,name) values(1,'abc');
insert into t1(id,name) values(2,'你好');（如果数据库默认编码方式为‘latin’，可能数据无法写入或乱码）
#通过show variables like 'character%';可以查看默认编码方式
删除表中数据：delete from t1 where name=abc;
修改表中数据：update t1 set name='abc';
update t1 set name='abc' where id=5;
查看表结构：show create table t1;
清空表内容：delete from t1; （不影响自增序列）
truncate table t1; （影响自增序列，从1开始重新排序；大表清空优先使用）
删除表：drop table t1;
外键：
create table department( id int auto_increment primary key, title char(15))engine=innodb default charset=utf8;
create table userinfo( uid int auto_increment primary key, name char(32), department_id int, constraint fk_user_depar foreign key (department_id) references department(id)) engine=innodb default charset=utf8;
#测试
select * from department;
+----+-------+
| id | title |
+----+-------+
| 1 | it |
| 2 | cc |
+----+-------+
insert into userinfo(name,department_id) values('user1',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`userinfo`, CONSTRAINT `fk_user_depar` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`))
#删除外键
alter table userinfo drop foreign key fk_user_depar;
#如果有其他表依赖你建立外键可以通过下面命令查看
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME='userinfo';
唯一索引：
#单一索引
create table admin(
id int auto_increment primary key,
user_id int not null,unique uq_ul (user_id),constraint fk_admin_u1 foreign key (user_id) references userinfo1(id),
password varchar(64) not null)engine=innodb default charset=utf8;
#如果表已创建可通过alter添加唯一索引
#联合唯一索引
alter table score add unique(student_id,corse_id);
#测试
insert into score(student_id,corse_id,number) values(1,1,60);
insert into score(student_id,corse_id,number) values(1,1,66);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'student_id'
数据操作补充
插入数据：
create table tb11(
id int auto_increment primary key,name varchar(32),
age int)engine=innodb default charset=utf8;
insert into tb11(name,age) values('aaa1',12);
#多条数据同时插入
insert into tb11(name,age) values('aaa2',13),('aaa3',18);
create table tb12(
id int auto_increment primary key,name varchar(32),
age int)engine=innodb default charset=utf8;
#将表tb11的数据读出后插入到表tb12
insert into tb12(name,age) select name,age from tb11;
删除数据：
delete from tb12 where id !=2;
delete from tb12 where id >=2 and name='aaa3';
修改数据：
update tb12 set name='ccc',age=19 where id >2 and name='***';
查询数据：
#查询ID是1、5、12的信息(not in查询除1、5、12以外的信息)
select * from tb12 where id in (1,5,12);
#查看ID从5到12的信息
select * from tb12 whete between 5 and 12;
#查询匹配信息
#查询以a开头的（%代表0次到N次，_代表一次）
select * from tb12 where name like 'a%';
#查询符合结果的前3条
select * from tb12 limit 3;
select * from tb12 where name like 'a%' limit 3;
#查询符合结果，从0位置开始往后的两条数据
select * from tb12 limit 0,2;
#排序
#根据ID列从小到大排序
select * from tb12 order by id asc;
#根据ID列从大到小排序
select * from tb12 order by id desc;
#多列排序(当age有重复值时再按ID进行排序)
select * from tb12 order by age desc,id desc;
分组：
create table department5(
id int auto_increment primary key,
title varchar(32))engine=innodb default charset=utf8;
insert into department5(title) values('公关'),('公公'),('关关'),('公共');
select * from department5;
+----+--------+
| id | title |
+----+--------+
| 1 | 公关 |
| 2 | 公公 |
| 3 | 关关 |
| 4 | 公共 |
+----+--------+
create table userinfo5(
id int auto_increment primary key,
name varchar(32),
part_id int,constraint fk_user_part foreign key (part_id) references department5(id)
)engine=innodb default charset=utf8;
insert into userinfo5(name,part_id) values('user1',2),('user2',4),('user3',1),('user4',3),('user5',2);
select * from userinfo5;
+----+-------+---------+
| id | name | part_id |
+----+-------+---------+
| 1 | user1 | 2 |
| 2 | user2 | 4 |
| 3 | user3 | 1 |
| 4 | user4 | 3 |
| 5 | user5 | 2 |
+----+-------+---------+
#查看userinfo5表中有那些部门（同一个部门只取ID最大的记录）
#（count计数、max最大值、min最小值、sum求和、avg平均值）
select max(id),part_id from userinfo5 group by part_id;
+---------+---------+
| max(id) | part_id |
+---------+---------+
| 3 | 1 |
| 5 | 2 |
| 4 | 3 |
| 2 | 4 |
+---------+---------+
#查看userinfo5表中每个部门的人数
select count(id),part_id from userinfo5 group by part_id;
+-----------+---------+
| count(id) | part_id |
+-----------+---------+
| 1 | 1 |
| 2 | 2 |
| 1 | 3 |
| 1 | 4 |
+-----------+---------+
#如果对聚合函数结果进行二次筛选时必须使用having，不能用where
select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
连表查询:
select * from score;
+-----+------------+----------+--------+
| sid | student_id | corse_id | number |
+-----+------------+----------+--------+
| 1 | 1 | 1 | 60 |
| 3 | 2 | 2 | 100 |
| 8 | 1 | 3 | 66 |
| 9 | 2 | 3 | 96 |
| 10 | 2 | 1 | 93 |
| 11 | 3 | 1 | 85 |
| 12 | 3 | 2 | 79 |
| 13 | 3 | 3 | 82 |
+-----+------------+----------+--------+
select * from student;
+-----+--------+--------+----------+
| sid | sname | gender | class_id |
+-----+--------+--------+----------+
| 1 | 钢蛋 | 女 | 1 |
| 2 | 铁锤 | 女 | 1 |
| 3 | 山炮 | 男 | 2 |
+-----+--------+--------+----------+
#如果要三表及以上直接在后面left join接着写就行
'''
select cname,number,tname from score
left join course on score.corse_id = course.cid
left join teacher on course.tearch_id = teacher.tid;
'''
select * from score left join student on score.student_id = student.sid;
+-----+------------+----------+--------+------+--------+--------+----------+
| sid | student_id | corse_id | number | sid | sname | gender | class_id |
+-----+------------+----------+--------+------+--------+--------+----------+
| 1 | 1 | 1 | 60 | 1 | 钢蛋 | 女 | 1 |
| 8 | 1 | 3 | 66 | 1 | 钢蛋 | 女 | 1 |
| 3 | 2 | 2 | 100 | 2 | 铁锤 | 女 | 1 |
| 9 | 2 | 3 | 96 | 2 | 铁锤 | 女 | 1 |
| 10 | 2 | 1 | 93 | 2 | 铁锤 | 女 | 1 |
| 11 | 3 | 1 | 85 | 3 | 山炮 | 男 | 2 |
| 12 | 3 | 2 | 79 | 3 | 山炮 | 男 | 2 |
| 13 | 3 | 3 | 82 | 3 | 山炮 | 男 | 2 |
+-----+------------+----------+--------+------+--------+--------+----------+
#查看用户权限
mysql> show grants for db;
+---------------------------------------------+
| Grants for db@% |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'db'@'%' |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'db'@'%' |
+---------------------------------------------+
2 rows in set (0.01 sec)
#用户授权
mysql> grant all privileges on db2.* to db@'%';
* 参考oldboy视频整理