数据库操作（DDL）
1.创建数据库；
create database [if not exists] db_name [character set xxx];
2.查看数据库；
show databases; 查看所有数据库
show create databases name; 查看数据库的创建
3.修改数据库；
alter database db_name [character set xxx];
4.删除数据库；
drop database [if exists] db_name;
5.切换数据库
use db_name;
6.设置密码
mysqladmin -uroot password '123'; #设置初始密码，初始密码为空因此-p选项没有用
mysqladmin -u root -p123 password '1234'; #修改root用户密码
7.启动mysql服务与停止mysql服务命令：
net start mysql
net stop mysql
数据类型
数值类型
日期类型
字符串类型
数据库表操作
1.基础操作
1.1 创建表
create table tab_name(
field1 type [约束条件],
field2 type
)
1.2 查看表信息
desc table_name;
show create table table_name;
1.3 添加修改表字段
ALTER TABLE employee1 ADD A int, add B VARCHAR(20);
1.4 删除表字段
ALTER TABLE employee1 DROP B , DROP entry_date;
1.5 修改字段类型约束
alter table employee1 modify age smallint not null default 18 after id;
alter table employee1 change department depart varchar(20) after salary;
1.6 修改表名
rename table employee1 to emp;
2.表操作之增删改查
2.1 插入数据
insert into emp values(20,"李四",1,2000,"销售部",1);
insert into emp(name,gender,salary,depart) values("马六",1,8000,"技术部"),("王五",1,5000,"综合部");
insert into emp set name="刘备", gender=1, salary=0,depart="三国";
2.2 修改数据
update emp set salary=salary+2000 where name="刘备";
2.3 删除数据
delete from emp; //删除整个表
truncate table emp; //删除表，创建一个空表
delete from emp where id=6;
delete from emp where id=2 or id=3; //删除2个，或操作
2.4 查询数据
select * from emp; // 从表中查询所有数据
示例：
create table ExamResult(
id int PRIMARY KEY auto_increment,
name VARCHAR(20),
JS DOUBLE,
Django DOUBLE,
flask double
);
insert into examresult values (1,"zhangsan", 98,99,22),(2,"lisi", 92,91,82),(3,"wangwu", 68,29,92),(4,"maliu", 98,99,88),(5,"liuqi", 78,25,52),(6,"qqing", 28,19,53);(7,"zhangsan", 98,99,22),
select DISTINCT name from examresult; // 去除重复
select name as "姓名",JS as "JS成绩",Django as "Django成绩",Flask as "Flask成绩" from examresult; //别名
select name, JS from examresult where JS>80; //查询大于某一值，
条件判断用 “> < >= <= <> !=” “between 10 and 20” “in (2,3,4)” “like ‘lisi%’”
insert into examresult value("曹操");
select name from examresult where JS is null;
select name, JS from examresult order by JS; //排序，默认升序，降序为后面加desc
select name, JS+Django+Flask as 总成绩 from examresult order by 总成绩 desc;
mysql在执行sql语句时的执行顺序：from、where、select、group by、having、order by。
2.5 分组查询
select name from examresult GROUP BY name;
select name,sum(JS) from examresult GROUP BY name; //按name分组，并对重复的name去求JS的和
select name,sum(Django) from examresult group by name having sum(Django)>150; //根据name分组，查询Django总分大于150
select * from examresult having id=3; //和where类同，不过放在group by后，效率低于where
聚合函数 sum、count、avg
select count(name) from examresult where JS>70;
select avg(JS) from examresult;
select count(name) from examresult where (ifnull(JS,0)+ifnull(Django,0)+ifnull(Flask,0)) >280; //ifnull(JS,0)函数，遇到null设置为0.
select max(JS) from examresult;
select min(ifnull(JS,0)) from examresult;
select * from examresult limit 3; //显示前3条
select * from examresult limit 1,4; // 跳过1条显示后面4条
2.6 正则查询
select * from examresult where name regexp '^zhan';
3.外键
3.1 创建外键
--主表
create table classcharger(
id TINYINT PRIMARY KEY auto_increment,
name VARCHAR(20),
age int,
is_marriged boolean
);
--插入数据
insert into classcharger values('zhangsan',22,false),('lisi',21,false),('zhangss',25,true);
--子表
create table stu(
id TINYINT PRIMARY KEY auto_increment,
name VARCHAR(20),
charger_id TINYINT,
FOREIGN KEY (charger_id) REFERENCES classcharger(id)
)ENGINE=INNODB;
--插入数据
insert into stu(name, charger_id) values("alvin1",1),("alvin2",2),("alvin3",2),("alvin4",1),("alvin5",1),("alvin6",3),("alvin7",3);
delete from classcharger where id=2; //有约束，删除不了，需要把子表中的关联的数据修改或删除。
insert into stu2(name, charger_id) values('aa',2); // 有约束，如果主表中没有id=2，就插入不了
3.2 修改外键
ALTER TABLE stu ADD CONSTRAINT 'stu_charger_id' FOREIGN KEY (charger_id) REFERENCES classcharger(id); // stu_charger_id为外键别名
3.3 删除外键
ALTER TABLE stu DROP FOREIGN KEY 'stu_charger_id';
3.3 级联删除
如果要删除主表的记录同时删除子表记录，需要在创建外键的时候添加on DELETE CASCADE，FOREIGN KEY (charger_id) REFERENCES classcharger(id) ON DELETE CASCADE
如果删除主表的记录时候不删除字表纪律，需要在创建外键的时候添加on DELETE SET NULL，FOREIGN KEY (charger_id) REFERENCES classcharger(id) ON DELETE SET NULL
4.连表查询
create table employee(
emp_id int auto_increment primary key not null,
emp_name varchar(50),
age int,
dept_id int
);
insert into employee(emp_name,age,dept_id) values
('A',19,200),
('B',26,201),
('C',30,201),
('D',24,202),
('E',20,200),
('F',38,204);
create table department(
dept_id int,
dept_name varchar(100)
);
insert into department values
(200,'人事部'),
(201,'技术部'),
(202,'销售部'),
(203,'财政部');
4.1 内连接
select employee.emp_name, department.dept_name
from employee,department
where employee.dept_id = department.dept_id and employee.emp_name="A";
select employee.emp_name, department.dept_name
from employee INNER JOIN department
on employee.dept_id = department.dept_id;
4.2 外连接
select employee.emp_name, department.dept_name
from employee LEFT JOIN department
on employee.dept_id = department.dept_id; // 左连接
select employee.emp_name, department.dept_name
from employee RIGHT JOIN department
on employee.dept_id = department.dept_id; // 右连接
select employee.emp_name,employee.age,department.dept_name from employee,department
where employee.age>25 and employee.dept_id=department.dept_id;
4.3 子查询
select * from employee where dept_id in (select dept_id from department); // in
create table AA (select * from employee where dept_id in (select dept_id from department));
select * from employee WHERE EXISTS(SELECT dept_name from department where dept_id=203); //exists 后面如果是true，才会执行前面的select，如果是false，就会返回空。
5.索引
5.1 索引分类
1.普通索引index :加速查找
2.唯一索引 主键索引：primary key ：加速查找+约束（不为空且唯一） 唯一索引：unique：加速查找+约束 （唯一）
3.联合索引 -primary key(id,name):联合主键索引 -unique(id,name):联合唯一索引 -index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用
5.2 创建索引
方法一：创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ;
方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;
5.3 删除索引
DROP INDEX 索引名 ON 表名字;
6.事务
事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败；
数据库开启事务命令
start transaction --开启事务
rollback --回滚事务 ，即撤销指定的sql语句（只能回退 insert delete update语句）
commit --提交事务， 提交为存储的事务
savepoint --保留点，事务处理中设置的临时占位符，可以对其发布退回。
6.1 示例
--创建表
create table test2(id int primary key auto_increment, name varchar(20));
--插入数据
insert into test2(name) values('zhangsan'),('lisi'),('wangwu');
--开启事务
start transaction;
--插入数据
insert into test2(name) values('liuliu');
insert into test2(name) values('chenqi');
--设置保留点
savepoint point1;
insert into test2(name) values('chenqi2');
savepoint point2;
delete from test2 where id=3;
savepoint point3;
--回滚至保留点
rollback to point2;