MySQL常用语法

1 SQL语言

DDL: create alter drop – 数据定义语言（DDL）
DML: insert update delete– 数据操纵语言（DML） 
TCL: commit rollback savepoint– 事务控制语言（TCL）
DQL: select– 数据查询语言（DQL）
DCL: create user grant revoke– 数据控制语言（DCL） 

2. 连接MySQL数据库

mysql -u root -p
--(-pabc123)如果登录时就选择填入密码
mysql -h localhost -u root -pabc123 mysql
--如果登录时就选择要操作的数据库则
mysql -h 主机地址 -u 用户名 -p 数据库名称 -P 端口号
mysql -h ip地址 -u root -p

3. 数据库管理

3.1 创建数据库
create database 数据库名;
create database choose;

create database choose character set=utf8;
//windows下改为CREATE DATABASE choose CHARSET = utf8;

3.2 查看数据库(编码格式等)
show databases;
show create database 数据库名;
例如:
show create database choose;

3.3 选择数据库
use 数据库名;
use choose;
3.4 删除数据库
drop database 数据库名;
3.5 删除表
drop table 表名;

4.表管理

4.1 创建表
create table my_table(
id int,
name char(10),
phone char(11)
);

4.2 查看表
show tables; # 查看当前数据库中的所有表
desc 表名; # 查看表结构
show create table 表名; # 查看表的详细信息

4.3 删除数据库表
drop table 表名;

5.约束

5.1 约束的类型
1) 主键约束 primary key
一个表中只能有一个主键约束
不允许重复 不允许为NULL
2) 唯一约束 unique
值不允许重复
3) 非空约束 not null
不允许为null
4) 检查约束 (MySQL目前不支持,可以通过触发器等实现)
5) 默认值约束 default
6) 外键约束 foreign key

5.2 主键约束
1) 单一字段做主键
create table test_constraint(
id int primary key,
name char(10)
);
2) 复合主键
drop table test_constraint;
# 一个表中只能有一个主键 错误
create table test_constraint(
id int primary key,
userid char(20) primary key,
name char(10)
);
# 复合主键
create table test_constraint(
id int,
userid char(20),
name char(10),
primary key(id,userid)
);

5.3 非空约束
drop table test_constraint;
create table test_constraint(
id int primary key,
name char(10) not null
);
5.4 默认约束
drop table test_constraint;
create table test_constraint(
id int primary key,
name char(10) not null,
age int default 20
);
5.5 唯一约束
drop table test_constraint;
create table test_constraint(
id int primary key,
userid char(18) unique not null
);
drop table test_constraint;
# 一个表中可以有多个唯一约束
create table test_constraint(
id int unique,
userid char(18) unique
);

5.6 外键约束

主表(父表): 提供数据的表 主键
从表(子表): 外键所在的表
外键字段的要么来自于主表中的对应字段,要么为null.
# 创建主表--班级表
create table classes(
class_no int primary key,
class_name char(20)
);
# 创建从表 -- 学生表
create table student(
stu_no int primary key,
stu_name char(10) not null,
class_no int,
foreign key(class_no) references classes(class_no)
);
1) 创建表时,先创建主表,再创建从表
2) 被引用的字段必须是主表的主键字段

5.7 为约束命名
语法:
constraint 约束名 约束规则
create table test(
t_no int primary key,
t_name char(10),
constraint test_name_uk unique(t_name)
);

# 外键
constraint 约束名 foreign key(字段名) references
主表名(主键字段名)

5.8 自增型字段（必须是主键拥有）
drop table test;
create table test(
t_no int auto_increment primary key,
name char(10) not null
);

6.dml语句

6.1 insert语句

# 向classes表中写入数据
insert into 
classes(class_no,class_name,department_name)
values(null,'2017自动化1班','机电工程');
--如果只给部分字段的值(括号内要指明需要给值的字段)
insert into classes(class_name,department_name)
values('2017自动化2班','机电工程');
--如果添加的字段与表的字段相同则不用写特定的字段
insert into classes
values(null,'2017自动化3班','机电工程');

# 插入多行数据

create table stu like student;
--以一个表的数据复制到另一个表()
insert into stu(student_no,student_name,student_contact)
select student_no,student_name,student_contact 
from student;

 6.2 update语句

 使用update语句可以对表的一行或多行进行修改，

语法：

update 表名 set 字段名1=值1,字段名2=值2,…,字段名n=值n [where 条件表达式];

  --更新对子表，主表都有影响
update score set c_score=c_score + 5;
update score set c_score=100 where c_score>100;
update stu set student_name='张三丰',class_no=2 where student_no='2017001';

--班号(class_no)影响以下修改会出错
update student set student_name='test',student_contact='1111',class_no=10
where class_no=1;
update classes set class_no=4 where class_no=1;

6.3 delete语句 

如果表中的某条（某几条）记录不再使用，可以使用 delete语句删除，

语法格式为： delete from 表名 [where 条件表达式]; 删除表记录时，需要注意表之间的外键约束关系

delete from stu where student_no='2017001';

6.4 truncate语句 

truncate table用于完全清空一个表，语法格式如下：

truncate [table] 表名; truncate语句和delete语句的区别：

使用truncate语句清空父表，将会失败

使用truncate语句清空表记录，会重置自增型字段的计数器 

 -- 创建测试表
  create table test(
    id int auto_increment primary key,
    name varchar(10));
  -- 插入测试数据
  insert into test(name) values('张三');
  insert into test(name) values('李四');
  insert into test(name) values('王五');
  -- 使用delete 删除数据时,自增长字段继续编号
  delete from test;
  insert into test(name) values('张三'); -- id为4
  -- 使用truncate截断表,自增长字段重新编号
  -- truncate语句(字段没有外键约束的情况下才能使用)
  truncate table test;
  insert into test(name) values('张三'); -- id为1

truncate语句不支持事务的回滚

7. DQL语句

7.1  select 字段列表 from 数据源 [ where 条件表达式] 

 select * from student;
 select student_no,student_name from student;
 select student_no as 学号,student_name 姓名 from student;

[ group by 分组字段[ having 条件表达式]] 

[ order by 排序字段 [asc | desc]] 

可以使用关键字as为字段或表达式命名别名 

语法： 字段（或表达式）[as] 别名

7.2  使用distinct过滤重复记录 

distinct 字段名

  select department_name 院系 from classes;
  select distinct department_name 院系 from classes;
  select distinct class_no,department_name 院系 from classes;

7.3   使用limit实现分页，语法格式如下：

select 字段列表 from 数据源 limit [start, ]length; 其中，

start表示从第几行记录开始检索 length表示检索多少行记录

以系统数据库information_schema中的tables为例：
  desc information_schema.tables;
  # 列出第一页(每页10行)
  select table_schema,table_name,table_rows from information_schema.tables limit 10;
  # 列出第二页
  select table_schema,table_name,table_rows from information_schema.tables limit 10,10; 

7.4 表连接

 使用join...on实现表连接

from 表名1 [连接类型] join 表名2 on  表1和表2之间的连接条件 

inner关键字可省略

select student.student_no 学号,student.student_name 姓名,classes.class_name 班级,classes.department_name 学院
  from classes inner join student 
     on student.class_no = classes.class_no;

可以给表名指定别名 如果两张表中有同名字段，字段名前需要用表名作前缀 

   # 表的别名
    select s.student_no 学号,s.student_name 姓名,
       c.class_name 班级,c.department_name 学院
     from classes c inner join student s
       on s.class_no = c.class_no;

 字段不重复的话表前缀也可省略

select student_no 学号,student_name 姓名,
       class_name 班级,department_name 学院
     from classes c inner join student s
       on s.class_no = c.class_no;

注意:在SQL语句中,表一旦命名别名,则该语句中只能使用别名,不能使用原表名

 7.5外链接
1)左外连接

– 左外连接的结果集=内连接的结果集+左表匹配不上的记录

– 语法： from 表1 left  join 表2 on 表1和表2之间的连接条件 

select s.student_no 学号,s.student_name 姓名,
       c.class_name 班级,c.department_name 学院
      from  student s left join classes c
        on c.class_no = s.class_no;

2) 右外连接
– 右外连接的结果集=内连接的结果集+右表匹配不上的记录

– 语法： from 表1 right  join 表2 on 表1和表2之间的连接条件

select s.student_no 学号,s.student_name 姓名,
       c.class_name 班级,c.department_name 学院
      from  student s right join classes c
        on c.class_no = s.class_no;

3) 完整外连接(全外连接)--目前MySQL不支持
full join ... on

 7.6 多表连接 

from子句可以指定多个数据源，实现多表连接，继而实现 从更多的表中（以3个表为例）检索数据，语法格式如下

from 表1 [连接类型] join 表2  on 表1和表2之间的连接条件 [连接类型] join 表3 on 表2和表3之间的连接条件

select s.student_no 学号,s.student_name 姓名,
      c.class_name 班级名称,ch.score 成绩
     from student s 
         join classes c on s.class_no=c.class_no 
         join choose ch on s.student_no=ch.student_no; 

7.7 where子句

1.where子句
1.1 单一条件
1.1.1 使用比较运算符
# 检索'2017自动化2班'的所有学生的信息,列出学号、姓名和班级名称。

select student_no,student_name,class_name
from student s join classes c on s.class_no=c.class_no
where class_name='2017自动化2班';

# 列出需要补考的学生的信息

select student_no,course_no,score from choose
where score<60;

1.2实现内连接 – 语法：
from 表1,表2 where 连接条件

    select student_no,student_name,class_name
      from student s,classes c
        where s.class_no = c.class_no;

    select student_no,student_name,class_name
      from student s,classes c
        where s.class_no = c.class_no
          and class_name='2017自动化2班'; #自动化2班'的所有学生的信息

# 使用where子句实现三表内连接
  select 字段列表
  from 表1，表2，表3
  where 连接条件1 and 连接条件2;

    #列出学生的学号、姓名、所在班级名称和考试成绩
     select s.student_no,student_name,class_name,score
       from  student s,classes c,choose ch
         where s.class_no = c.class_no 
           and s.student_no = ch.student_no;

1.1.3 sql中提供的运算符
1）between.. and
表示一个闭区间 [a,b]
where 字段名 between a and b
# 列出成绩在[50,60]之间的信息

select student_no,course_no,score from choose 
where score between 50 and 60;

2) is null
语法：
where 字段名 is null
# 列出没有班级的学生

select student_no,student_name from student
where class_no is null;

3) in

in运算符用于判断一个表达式的值是否位于一个离散的 数学集合中
语法：
where 字段名 in(值1,值2,..)
# 列出班级编号为1或2的学生

select student_no,student_name,class_no from student
where class_no in(1,2);

# 列出姓张或田 的学生

select student_no,student_name,class_no from student
where substring(student_name,1,1) in('张','田');

8.表结构

8.1  复制表结构
在create table语句的末尾添加like子句
– 语法：
create table 表名 like 源表

 结构和数据一起复制
create table 表名 select * from 源表

8.2  修改表结构 ----修改字段的相关信息
删除字段，语法：
alter table 表名 drop 字段名;

alter table s drop class_no;

添加新字段，语法：
alter table 表名 add 新字段名 数据类型 [约束条件] [first|after 旧字段名]

alter table c1 add userid char(18) after id;#放到id字段后边
alter table c1 add userid char(18);  #默认加到最后

8.3 修改字段

修改字段名（及数据类型），语法：
alter table 表名 change 旧字段名 新字段名 数据类型
– 仅修改字段的数据类型，语法：
alter table 表名 modify 字段名 数据类型

8.4 -修改约束
添加约束条件 – 语法:
alter table 表名 add constraint 约束名 约束类型（字段名）

alter table c1 add constraint c1_name_u unique(name);

删除约束条件 – 删除主键约束
alter table 表名 drop primary key

– 删除外键约束
alter table 表名 drop foreign key 约束名

删除唯一约束
alter table 表名 drop index 唯一索引名

alter table c1 drop index c1_name_u;#通过索引删除，c1_name_u为起的索引名

改表名
rename table 旧表名 to 新表名
该命令等效于
alter table 旧表名 rename 新表名