Mysql高级05-SQL语句

SQL

库结构操作SQL

 1、查看所有数据库

show databases;

2、切换使用数据库

use 数据库名;

 3、创建数据库

create database 数据库名;
create database 数据库名 charset 'utf8';

4、删除数据库

drop database 数据库名;

表结构操作SQL

 1、在某数据库中查看所有表格

show tables; 

 2、查看表结构

desc 表名称;

3、删除表结构

drop table 表名称;

4、修改表名称

rename table 旧名称 to 新名称;

5、修改表字段

（1）增加一个字段

alter table 表名称 add  字段名称  数据类型; #默认在最后
alter table 表名称 add  字段名称  数据类型 after 另一个字段名; 
alter table 表名称 add  字段名称  数据类型 first;

（2）删除一个字段

alter table 表名称 drop  字段名称;

（3）修改字段的名称

alter table 表名称 change  旧字段名称  新字段名称 数据类型;

（4）修改字段的数据类型

alter table 表名称 modify  字段名称  数据类型;

（5）修改字段的位置

alter table 表名称 modify  字段名称  数据类型 after 另一个字段名; #指定特定位置
alter table 表名称 modify  字段名称  数据类型 first;

6、创建表格

create table 表名称(
   字段名 数据类型 primary key auto_increment,   #(自增)必须是整数列、必须是键列、只能有一列
   字段名 数据类型 unique key not null,          #(非空)值是非空、可以有多列
 　字段名 数据类型 default 默认值,  　　　　　　　　 #(默认值)设置默认值、可以有多列       
   primary key(字段名...),　　 　　　　 　　 　 　 #(主键)必须唯一、非空、只能有一个
 　unique key(字段名...),　　                 　 #(唯一键)值唯一、不要求必须非空、可以有多个
   foreign key(参考列名) references 主表名(被参考列名) 【on update xxx on delete yyy】　#从表上
　 #(外键)限定两个表的两个字段之间的值或一个表的两个字段之间的值的约束关系　　　　　　　　　　　　　
);

7、建表后修改表约束

（1）修改主键

alter table 表名称 add primary key (字段名...);

（2）删除主键

alter table 表名称 drop primary key;

（3）修改唯一键

alter table 表名称 add unique key(字段列表);

（4）删除唯一键，是通过删除对应的索引来删除的

alter table 表名称 drop index 索引名;

（5）修改表非空

alter table 表名称 modify 字段名 数据类型 not null;

（6）取消非空约束

alter table 表名称 modify 字段名 数据类型 【default 默认值】;

（7）修改默认值

alter table 表名称 modify 字段名 数据类型 default 默认值;

（8）取消默认值

alter table 表名称 modify 字段名 数据类型 【not null】;

注意：当通过修改语句，增加或取消默认值或非空，如果要保留默认值或非空约束，还得写上。

（9）修改自增

alter table 表名称 modify 字段名 数据类型 auto_increment;

（10）删除自增

alter table 表名称 modify 字段名 数据类型;

（11）修改外键

alter table 主表名称  add foreign key (主表列) references 被引用表名(被引用列名) 【on update xx on delete yy】;

（12）删除外键

alter table 主表名称 drop foreign key 外键约束名;　　　　约束英文名为(CONSTRAINT_NAME)

数据操作

增

1、为所有字段赋值，一次添加多行或一行

insert into 表名称 values(值1,值2...);　　
insert into 表名称 values(值1,值2...),(值1,值2...),...;

2、为指定字段赋值，一次添加多行或一行

insert into 表名称(字段1,字段2...) values(值1,值2...);
insert into 表名称(字段1,字段2...) values(值1,值2...),(值1,值2...),...;

#要求值的数量、顺序与表结构的数量、顺序一一对应

#默认值的位置可以写default，自增的位置写null

删

1、删除整张表的数据

delete from 表名称;
truncate 表名称;

2、删除满足条件的行

delete from 表名称 where 条件; 

#delete是一条一条删除。truncate是把整张表drop掉，重新建一张。
#delete如果在事务中，可以回滚。truncate是无法回滚。

改

1、修改所有行的部分字段

update 表名称 set 字段名 = 字段值, 字段名 = 字段值...; 

2、修改满足条件的行

update 表名称 set 字段名 = 字段值, 字段名 = 字段值... where 条件;

查

select * from 表名称;

运算符

is null
is not null

与：&& and
或：|| or

between .. and ..
in(....)

not between .. and ..
not in(....)

like 配合 '%或_'
_代表一个字
%代表任意个字

条件查询

只返回不同的值

select distinct name from users;

返回按照第1列的升序排序，desc降序，如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。

select *  from  users order by 1 【desc】;

返回从第1行起的5行数据，第一个被检索的行是第0行

select * from users limit 0,5;

返回 “xiaoming-小明” 拼接字样的一列数据

select concat (username,'-',NAME) from users;

查看某个表的索引

show index from 表名称;

查看某个表的所有约束

select * from information_schema.table_constraints where table_name = '表名称';

完整语句

select distinct
    <select_list>
from
    <left_table> <join_type>
join <right_table> on <join_condition>
where
    <where_condition>
group by
    <group_by_list>
having
    <having_condition>
order by
    <order_by_condition>
limit <limit_number>

1、外键约束的等级
（1）Restrict方式：严格模式，默认的模式
（2）No action方式：在mysql和Restrict方式一样
（3）Set null方式：置空
（4）Cascade方式：级联

可以分别设置on update xxx on delete yyy。
修改和删除的约束等级可以相同也可以不同。

2、外键约束的操作
（1）建表的顺序：先建立主表，再建立从表
（2）添加和修改从表的记录时，参考列的值必须在被参考列的值的范围内选择
例如：添加和修改员工表记录时，员工所在的部门的选择，要在部门表的编号范围内
（3）修改和删除主表的记录时，要看约束等级，
如果是Restrict方式，那么只要被参考列的某个值被引用了，就不能随意删或改。
如果是Set null方式方式，那么主表对应的值被修改和删除了，从表对应的记录就设置为null
如果是Cascade方式，那么主表对应的值被修改或删除了，从表对应的记录跟着修改或删除。
（4）删除表顺序：先删从表，再删主表