-- 创建数据库，判断不存在，再创建;
create database if not exists 数据库名称;
-- 创建数据库，并且指定字符集;
create database 数据库名称 character set 字符集名称;
-- 创建数据库db，判断是否存在，并指定字符集为gbk
create database if not exists db character set gbk;
删除数据库
使用普通用户登录 MySQL 服务器，你可能需要特定的权限来创建或者删除 MySQL 数据库，所以我们这边使用 root 用户登录，root 用户拥有最高权限。
在删除数据库过程中，务必要十分谨慎，因为在执行删除命令后，所有数据将会消失。
-- 语法
drop database <数据库名>;
-- 例如删除名为 test 的数据库：
drop database test;
-- 判断数据库是否存在，存在则删除;
drop database if exists 数据库名称;
查询数据库
-- 查看全部
show databases;
-- 查看部分（模糊查询）
show databases like 'pattern';
其中，pattern 是匹配模式，有两种，分别为：
%：表示匹配多个字符;
_：表示匹配单个字符。
此外，在匹配含有下划线 _ 的数据库名称的时候，需要在下划线前面加上反斜线 _ 进行转义操作。
-- 匹配所有 TBL 开头的数据库。
show databases like 'TBL%';
-- 查看数据库的创建语句
show create database 数据库名称;
-- 在这里，查看的结果有可能与咱们书写的 SQL 语句不同，这是因为数据库在执行 SQL 语句之前会优化 SQL，系统保存的是优化后的结果。
更新数据库
在这里，需要注意：数据库的名字不可以修改。
-- 语法
alter database 数据库名称 [库选项];
-- 修改test数据库的字符集为 gbk.
alter database test charset gbk;
选择数据库
在你连接到 MySQL 数据库后，可能有多个可以操作的数据库，所以你需要选择你要操作的数据库。
-- 使用 test 数据库
use test;
执行以上命令后，你就已经成功选择了 test 数据库，在后续的操作中都会在 test 数据库中执行。
create table if not exists test.student(
name varchar(10),
age int, /* 整型不需要指定具体的长度 */
grade varchar(10) /* 最后后一行，不需要加逗号 */
)charset utf8;
第 2 种：隐式的指定表所属的数据库，示例
use test; /* use + 数据库名称，表示切换到指定的数据库，这句命令其实不加分号也可以，但不建议这么做 */
create table if not exists student(
name varchar(10),
age int, /* 整型不需要指定具体的长度 */
grade varchar(10) /* 最后后一行，不需要加逗号 */
)charset utf8;
创建 MySql 的表时，表名和字段名外面的符号 ` 不是单引号，而是英文输入法状态下的反单引号，也就是键盘左上角 esc 按键下面的那一个 ~ 按键
反引号是为了区分 MySql 关键字与普通字符而引入的符号，一般的，表名与字段名都使用反引号。
删除表
MySQL 中删除数据表是非常容易操作的， 但是你再进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。
-- drop table table_name : 删除表全部数据和表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM;
drop table student;
-- 判断表是否存在，若存在则删除;
drop table if exists 表名称;
-- truncate table table_name : 删除表全部数据，保留表结构，立刻释放磁盘空间 ，不管是 Innodb 和 MyISAM;
truncate table student;
-- delete from table_name : 删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;
delete from student;
-- delete from table_name where xxx : 带条件的删除，表结构不变，不管是 innodb 还是 MyISAM 都不会释放磁盘空间;
delete from student where T_name = "张三"; -- 实例，删除学生表中姓名为 "张三" 的数据：
-- delete 操作以后，使用 optimize table table_name 会立刻释放磁盘空间，不管是 innodb 还是 myisam;
delete from student where T_name = "张三"; -- 实例，删除学生表中姓名为 "张三" 的数据：
-- 实例，释放学生表的表空间：
optimize table student;
delete from 表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。
总结
当你不再需要该表时， 用 drop;
当你仍要保留该表，但要删除所有记录时， 用 truncate;
当你要删除部分记录时， 用 delete。
查询表
-- 查看全部
show tables;
-- 查看部分（模糊查询）
show tables like 'pattern';
其中，pattern 是匹配模式，有两种，分别为：
%：表示匹配多个字符;
_：表示匹配单个字符。
此外，在匹配含有下划线 _ 的表名的时候，需要在下划线前面加上反斜线 \_ 进行转义操作。
-- 表示匹配所有以 t 结尾的表。
show tables like '%t';
-- 查看表的创建语句
show create table 表名;
-- 查看表中的字段信息
show columns from 表名;
-- 查询表的结构
desc 表名;
更新表
-- 修改表名
rename table 旧表名 to 新表名;
-- 修改表选项
alter table 表名 表选项[=] 值;
-- 修改表的字符集
alter table 表名 character set 字符集名称;
-- 新增字段
alter table 表名 add [column] 列名 数据类型 [列属性][位置];
-- 其中，位置表示此字段存储的位置，分为 first（第一个位置）和 after + 列名（指定的字段后，默认为最后一个位置）.
alter table student add column id int first;
-- 只修改列的数据类型;
alter table 表名 modify 列名 数据类型 [列属性][位置];
-- 其中，位置表示此字段存储的位置，分为 first（第一个位置）和 after + 列名（指定的字段后，默认为最后一个位置）.
alter table student modify name char(10) after id;
-- 即修改列名，也修改该列的数据类型
alter table 表名 change 旧列名 新的列名 新的数据类型 [列属性][位置];
-- 其中，位置表示此字段存储的位置，分为 first（第一个位置）和 after + 列名（指定的字段后，默认为最后一个位置）.
alter table student change grade class varchar(10);
-- 删除字段
alter table 表名 drop 列名;
alter table student drop age;
-- 注意：如果表中已经存在数据，那么删除该字段会清空该字段的所有数据，而且不可逆，慎用。
复制表
如果我们需要完全的复制 MySQL 的数据表，包括表的结构，索引，默认值等。 如果仅仅使用 CREATE TABLE ... SELECT 命令，是无法实现的。
CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;
可以拷贝一个表中其中的一些字段:
CREATE TABLE newadmin AS
(
SELECT username, password FROM admin
)
可以将新建的表的字段改名:
CREATE TABLE newadmin AS
(
SELECT id, username AS uname, password AS pass FROM admin
)
可以拷贝一部分数据:
CREATE TABLE newadmin AS
(
SELECT * FROM admin WHERE LEFT(username,1) = 's'
)
可以在创建表的同时定义表中的字段信息:
CREATE TABLE newadmin
(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)
AS
(
SELECT * FROM admin
)
或
create table 新表 select * from 旧表
整体方法
步骤一: 获取数据表的完整结构。
mysql> SHOW CREATE TABLE test_tbl \G;
*************************** 1. row ***************************
Table: test_tbl
Create Table: CREATE TABLE `test_tbl` (
`test_id` int(11) NOT NULL auto_increment,
`test_title` varchar(100) NOT NULL default '',
`test_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY (`test_id`),
UNIQUE KEY `AUTHOR_INDEX` (`test_author`)
) ENGINE=InnoDB
1 row in set (0.00 sec)
insert into 表名(字段列表)
values(值列表)[,(值列表)];
-- 示例:
insert into test(age,name)
valus(18,'guo');
删除数据
-- 删除满足条件的信息
DELETE FROM table_name [WHERE Clause]
-- 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。可以在 WHERE 子句中指定任何条件.
-- 删除 id 为 3 的行
delete from students where id=3;
-- 删除所有年龄小于 21 岁的数据
delete from students where age<20;
-- 删除表中的所有数据
delete from students;
修改数据
-- 如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
-- 示例:
update test
set age = 20
where name = 'guo';
-- 将 id 为 5 的手机号改为默认的
update students
set tel=default
where id=5;
-- 将所有人的年龄增加 1
update students
set age=age+1;
-- 将手机号为 13288097888 的姓名改为 "小明", 年龄改为 19:
update students
set name="小明", age=19
where tel="13288097888";
查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用 WHERE 语句来设定查询条件。
使用星号（*）来代替其他字段，SELECT 语句会返回表的所有字段数据
使用 WHERE 语句来包含任何条件。
使用 LIMIT 属性来设定返回的记录数。
通过 OFFSET 指定 SELECT 语句开始查询的数据偏移量。默认情况下偏移量为 0。
基础查询
-- 多个字段的查询
select 字段1，字段2，…… from 表名;
-- 去除重复
select distinct 字段名 from 表名;
-- 条件查询
where 子句后面跟条件
运算符
大于、小于、>=、<=、=、<>(不等于)
between...and
in(集合)
like：模糊查询
占位符
_：可以代替任意一个字符;
%：可以代替任意多个字符;
and 或 &&
or 或 ||
not 或 ！
-- 查询年龄大于等于20
select * from stu where age >= 20;
-- 查询年龄不等于20
select * from stu where age <> 20;
select * from stu where age != 20;
-- 查询年龄大于等于20，小于等于30的;
select * from stu where age between 20 and 30;
select * from stu where age >= 20 && age <= 30;
-- 查询年龄22岁、19岁、25岁;
select * from stu where age = 22 or age = 19 or age = 25;
select * from stu where age in (22, 19, 25);
-- 查询英语成绩为null;
select * from stu where English is null;
-- 注意事项：不能写=null;
-- 查询英语成绩不为null;
select * from stu where English is not null;
-- 注意事项：不能写!=null;
-- 查询姓张的人：
select * from stu where name like '张%';
-- 查询名字里面第二个字是三的人;
select * from stu where name like '_三%';
-- 查询名字是三个字的人
select * from stu where name like '_ _ _';
-- 查询名字里面包含张的人
select * from stu where name like '%张%';
排序查询
-- 语法
order by 子句;
-- 按照数学成绩升序排序;
select * from stu order by math;
select * from stu order by math ASC;
-- 按照数学成绩升序排名，如果数学成绩一样，则按照英语成绩升序排名;
select * from stu order by math ASC，English ASC;
排序方式：
ASC：升序（默认就是升序）;
DESC：降序
注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件;
聚合查询
-- count：计算个数;一般选择非空的列：主键
select count(ifnull(id,0)) from stu;
-- count(*)：不建议使用;
-- max：计算最大值;
select max(english) from stu;
-- min：计算最小值;
select min(english) from stu;
-- sum：计算和;
select sum(english) from stu;
-- avg：计算平均值;
select avg((ifnull(english,0)) from stu;
分组查询
-- 语法
group by 子句;
-- 按照性别分组，分别查询男生和女生的平均成绩
select sex，avg(ifnull(english,0)) from stu group by sex;
-- 按照性别分组，分别查询男生和女生的平均成绩
select sex, avg(ifnull(english,0))，count(ifnull(id,0)) from stu group by sex;
-- 按照性别分组，分别查询男生和女生的平均成绩，以及人数。要求：分数低于70分的人不参与分组;
select sex, avg(ifnull(english,0))，count(ifnull(id,0)) from stu where english > 70 group by sex;
-- 按照性别分组，分别查询男生和女生的平均成绩，以及人数。要求：分数低于70分的人不参与分组,且分组之后，该组人数要大于2;
select sex, avg(ifnull(english,0))，count(ifnull(id,0)) from stu where english > 70 group by sex having count(ifnull(id,0)) > 2;
select sex, avg(ifnull(english,0))，count(ifnull(id,0)) as 人数 from stu where english > 70 group by sex having 人数 > 2;
where 和 having 的区别：
where 在分组之前进行限定，如果不满足条件，则不参与分组。having 在分组之后进行限定，如果不满足结果，则不会被查询出来;
where 后面不能跟聚合函数，having 可以跟聚合函数的判断;
分页查询
-- 语法
limit 开始索引，每页查询的条数;
-- 每页显示3条
select * from stu limit 0,3;
模糊查询
-- LIKE 子句
SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
-- 查询以 java 字段开头的信息.
SELECT * FROM position WHERE name LIKE 'java%';
-- 查询包含 java 字段的信息.
SELECT * FROM position WHERE name LIKE '%java%';
-- 查询以 java 字段结尾的信息.
SELECT * FROM position WHERE name LIKE '%java';
-- 查询所有员工信息和对应的部门信息;
select * from emp, dept where emp.dept_id = dept.id;
-- 查询员工表的姓名，性别、部门表的名称;
select emp.name, emp.sex, dept.name from emp, dept where emp.dept_id = dept.id;
-- 简化
select
t1.name,
t1.sex,
t2.name
from
emp t1,
dept t2
where
t1.dept_id = t2.id;
显示内连接
-- 语法
select 字段列表 from 表名1 inner join 表名2 on 条件;
-- 查询所有员工信息和对应的部门信息;
select * from emp inner join dept on emp.dept_id = dept.id;
-- -- inner可以省略，并且这个也可以像上面那样起别名;
外连接
左外连接
-- 语法
select 字段列表 from 表1 left outer join 表2 on 条件; -- 注意：outer可以省略不写
-- 查询的范围：查询的是左表所有的信息，以及其与右表的交集部分;
select t1.*, t2.name from emp t1 left join dept t2 on t1.dept = t2.id;
右外连接
-- 语法
select 字段列表 from 表1 right outer join 表2 on 条件; -- 注意：outer可以省略不写
-- 查询的范围：查询的是右表所有的信息，以及其与左表的交集部分;
select t1.* t2.name from emp t1 right join dept t2 on t1.dept = t2.id;
子查询
概念：查询中嵌套查询，称嵌套查询为子查询。
-- 查询工资最高的员工信息;
-- 传统写法
-- 首先查询最高工资是多少
select max(工资) from emp; -- 假设查询出来最高工资是9000;
-- 然后将查询出来的信息作为条件在进行查询;
select * from emp where emp.工资 = 9000;
-- 子查询方式
select * from emp where emp.工资 = (select max(工资) from emp);
子查询的不同情况
子查询的结果是单行单列的;
-- 子查询可以作为条件，使用条件运算符去判断;
-- 查询工资小于平均工资的员工信息;
select * from emp where emp.工资 < (select avg(ifnull(工资, 0)) from emp);
子查询的结果是多行单列的;
-- 子查询可以作为条件，使用条件运算符去判断;
-- 查询市场部、销售部所有的员工信息;
select * from emp where emp.dept_id in (select dept_id from dept where name in ("市场部", "销售部"));
子查询的结果是多行多列的;
-- 子查询可以作为一张虚拟的表参与查询;
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息;
select * from dept t1, (select * from emp where emp.date > "12-11-11") t2 where t1.id = t2.dept_id;
-- 普通查询
select * from emp t1, dept t2 where t1.id = t2.dept_id and t1.date > "2011-11-11";
DCL 管理用户、授权
管理用户
添加用户
-- 语法：create user '用户名'@'主机名' identified by '密码';
create user 'test' @ 'localhost' identified by '123';
删除用户
-- 语法：drop user '用户名' @ '主机名';
drop user 'test' @ 'localhost';
修改用户密码
-- 方法一：
update user set password = password('新密码') where user = '用户名';
-- 方法二：
set password for '用户名'@'主机名' = password('新密码');
查询用户
-- 切换到 mysql 数据库
use mysql;
-- 切换到 mysql 数据库
select * from user;
约束
描述
对表中的数据进行限定，保证数据的正确性、有效性、完整性。
非空约束
关键字：not null
作用：某一列的值不能为null;
-- 创建表时添加约束;
create table stu(
name varchar(4) not null
);
-- 表创建好后，添加非空约束;
create table stu(
name varchar(4)
);
alter table stu modify name varchar(4) not null;
-- 删除约束
alter table stu modify name varchar(4); -- 将姓名的非空约束删除
唯一约束
关键字：unique;
作用：某一列的值不能重复;
-- 创建表时添加唯一约束
create table stu(
phone_number varchar(11) unique
);
-- 表创建好后，添加唯一约束;
alter table stu modify phone_number varchar(11) unique;
-- 删除唯一约束
alter table stu drop index phone_number;
-- 创建表时添加主键约束;
create table stu(
id int primary key
);
-- 表创建好后添加主键约束;
alter table stu modify id int primary key;
-- 删除主键
alter table stu drop primary key;
注意事项：
一张表只能有一个字段为主键;
但是可以设置为多个字段为主键，也即联合主键;
主键就是表中记录的唯一标识;
自动增长
关键字：auto_increment;
作用：如果某一列是数值型的，可以完成值的自动增长。
-- 在创建表时添加自动增长;
create table stu (
id int auto_increment
);
-- 表创建好后添加自动增长
alter table stu modify id int auto_increment;
-- 删除自动增长
alter table stu modify id int;
-- 这样不会删除掉主键约束
这个值的增长，是按照上一条的数据进行增长。如果上一条数据是5，那么下一条就是6。
一般情况下，自动增长和主键一起使用;
外键约束
关键字：foreign key;
作用: 可定义表间以及表内必需的关系
-- 在创建表时添加外键约束
create table emp(
dep_id int
constraint 起一个新的名称 foreign key (外键名称) references 主表名称(主表列名称);
);
--主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_id INT,--外键名称
CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
-- 创建表后添加外键;
alter table employee add CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id);
-- 删除外键;
alter table employee drop foreign key emp_dep_fk;
-- 级联操作
-- 添加外键，设置级联更新;
alter table employee add CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) on update cascade;
-- 添加外键，设置级联更新，设置级联删除;
alter table employee add CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) on update cascade on delete cascade;