mysql中操作表的常用sql（*）

一.数据库相关

1.连接数据库
mysql -u 用户名 -p 输入密码

2.查询所有的数据库
show databases;

3.选择(使用)数据库
use 数据库名;

4.查询当前正在使用的数据库
select database();

5.展示所有的数据库表
show tables;

6.创建数据库
（1）create database 数据库名称;
创建数据库,判断不存在,再创建：create database if not exists 数据库名称;

（2）创建数据库并指定字符集
create database 数据库名称 character set utf8

（3）修改数据库编码
alter database 数据库名称 character set utf8

7.删除数据库
drop database 数据库名称;
判断数据库是否存在，存在再删除: drop database if exists 数据库名称;

8.查询当前时间
select now();

二.数据库表相关操作

1.创建数据库表
格式：
create table 表名(
字段名 类型(长度) 约束,
字段名 类型(长度) 约束
);

删除表格和创建表格操作

drop table if exists student;
create table student(
    Sno int not null auto_increment primary key comment '学号',
    Sname varchar(10) not null,
    Sex char(1) not null,
    Sage tinyint(100) not null,
    Sdept char(4) not null
)comment = '学生表';
alter table Student auto_increment = 201215121;

2.复制表
create table 表名 like 被复制的表名;

3.查看数据库表结构
desc 表名;
describe 表名；
show columns from 表名;

4.修改表名
alter table 表名 rename to 新的表名;
rename table 原表名 to 新表名;

5.修改表的字符集
alter table 表名 character set 字符集;

6.删除表
drop table 表名;
drop table if exists 表名;

7.字段操作
（1）添加一列
ALTER TABLE 【表名】 ADD 【列名】 【类型】
例如：alter table user add status tinyint(1) not null DEFAULT ‘0’ comment ‘状态 0禁用 1启用’

（2）删除列
ALTER TABLE 【表名】 DROP 【列名】;
例如：alter table user drop status

（3）修改列
1)修改列的类型、长度、约束
alter table exam modify history double(7,2);

2)修改表的列名
ALTER TABLE 【表名】 CHANGE 【列名】【新名】 类型
在 change关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型
alter table exam change history physics int not null;

3)修改字段默认为空和默认不为空
ALTER TABLE 表名 MODIFY column 字段名 int(10) NOT NULL COMMENT ‘’;
ALTER TABLE 表名 MODIFY 字段名 int(10) DEFAULT NULL COMMENT ‘’;

9.添加数据
insert into 表名(列名1,列名2,…列名n) values(值1,值2,…值n);
例如：insert into user values (1,‘李四’,‘123’); // 增
其中列名和值要一一对应。如果表名后，不定义列名，则默认给所有列添加值,如:insert into 表名 values(值1,值2,…值n);除了数字类型，其他类型需要使用引号(单双都可以)引起来.

10.删除数据
delete from 表名 where 条件
delete from user where id= 1; // 删
其中:如果不加条件，则删除表中所有记录。如果要删除所有记录, 使用delete from 表名; 一般不推荐使用。这种操作有多少条记录就会执行多少次删除操作。
TRUNCATE TABLE 表名;推荐使用，效率更高 先删除表，然后再创建一张一样的表.

11.修改数据
update 表名 set 列名1 = 值1, 列名2 = 值2,… where 条件;
例如：update user set name = ‘张三’ where id= ‘1’; //改
如果不加任何条件，则会将表中所有记录全部修改.

12.查询数据（多表查询两种语法）
格式：SELECT field1, field2,…fieldN FROM table_name1, table_name2…[WHERE condition1 [AND [OR]] condition2…
（1）select：查询
（2）from：数据源
（3）distinct：去重
（4）as：别名
（5）where：条件
（6）> 、< 、<= 、>= 、= 、<>或!=
（7）BETWEEN…AND（包含边界）
（8） IN( 集合)、NOT IN（集合）；EXISTS、NOT EXISTS
（9）LIKE 模糊查询；_单个任意字符、%多个任意字符
（10）IS NULL 、IS NOT NULL
（11）and 或 &&、or 或 || 、not或!
（12）聚合函数：Count()、Max()、Min()、Avg()、Sum()
（13）group by（具有去重的功能）：分组
（14）having：条件
（15）order by：排序
（16）limit：分页
（17）多表查询：inner join、left join、 right join
（18）子查询：构建临时表

13.mysql查询超时解决
select /+QUERY_TIMEOUT(150000000000)/ * from table_name
sql加上+/+QUERY_TIMEOUT(150000000000)/

14.使用explain分析查询的sql语句
explain select 语句
desc select 语句

15.创建表一定要给每个字段加注释，通过comment ‘注释’ 实现

16.能用sql语句解决的问题尽量使用sql，而不是写代码。

17.mysql数据库表，主键的查询效率是大于普通索引的

18.今天建的索引，明天才能生效。使用explain检测索引是否已经生效

19.处理大数据量数据，使用分区、分表、分库操作

20.sql语句执行顺序
from：数据源
join：连接表
on：连接条件
where：未分组的条件过滤
group by（开始使用select中的别名,后面的语句中都可以使用）：分组
avg、sum…：聚合函数
having：分组后条件过滤
select：查询的字段
distinct：去重
order by：查询到的数据进行排序
limit：分页显示

三.约束相关

1.主键约束 (primary key)
能够唯一确定一张表中的一条记录,我们通过给某个字段添加约束, 可以使得这个字段不重复且不为空.
create table user (
id int primary key auto_increment, // 在创建表时，添加主键约束，并且完成主键自增
name varchar(20)
);
– 联合主键: 由多个字段联合组成的主键, 只要联合的主键加起来不重复就可以.联合主键中的任何一个字段都不能为空.
create table user2 (
id int,
name varchar(20),
password varchar(20),
primary key(id, name)
);
表创建完成后，添加主键.如:
①alter table user add primary key(id);
②alter table user modify id int primary key;
删除主键:alter table user drop primary key;

2.唯一约束：unique 约束修饰的字段的值不可以重复.
create table user1 (
id int primary key auto_increment,
phone_num varchar(20) unique
);
create table user2 (
id int primary key auto_increment,
name varchar(20),
unique(id, name) // 表示两个字段在一起不重复就可以
);
也可以在表创建完成后, 通过
alter table user3 add unique(phone_num);或
alter table user3 modify phone_num varchar(20) unique;
删除unique约束:alter table user3 drop index phone_num;

3.非空约束：not null 修饰的字段不能为空（NULL）
create table user3 (
id int primary key auto_increment,
name varchar(20) not null
);
删除非空约束:alter table user3 modify name varchar(20);

4.默认约束：当我们插入字段值的时候,如果对应的字段没有插入值,则会使用默认值.如果传入了值,则不会使用默认值.
create table user4(
id int primary key auto_increment,
age int default 18,
name varchar(20) not null
);

5.外键约束
create table 表名(
…
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
// 班级
create table classes(
id int primary key,
name varchar(20)
);
// 学生表
create table student (
id int primary key,
name varchar(20),
class_id int,
foreign key(class_id) references classes(id)
);

四.索引

1.查看索引
show index from table_name

2.创建普通索引
create index 索引名 on 表名(字段名)
alter table 表名 add index 索引名(字段名)

3.创建联合索引
alter table 表名 add index 索引名(字段名1, 字段名2)

4.创建主键索引
alter table 表名 add primary key(字段名)

5.删除索引
drop index index_name on table_name
alter table 表名 drop index 索引名

6.创建唯一索引
create unique index 索引名 on 表名(字段名);

1.构建临时表。
2.操作表格的列（字段）
3.使用mysql查询数据时，通过构建临时表来存储数据，当数据量过大时，就会内存溢出。
4.一行就是一条记录。
5.一张表可以变化成多张表和自己相关联
6.通过构建符合条件的临时表来查询数据

查看mysql版本
SHOW VARIABLES LIKE ‘version’;

附加：
1.表关系：一对一、一对多或多对一、多对多
在关系数据库中,除了一对一,一对多外,还有一个多对多关系.
前两个关系都只需要两个表本身就能表达清楚他们之间的关系,而多对多关系则需要第三张中间表来表达清楚两张表之间的关系.
在一般情况下是由三个字段组成:

• 中间表本身的自增主键
• 两张表各自的主键

这时候命名就成为了一个问题，而采用双下划线就能解决这个问题。

2.数据库表干什么？
数据库表用来存储数据。
一个数据库表由多个字段构成。一个数据库表可以存储固定字段的多条记录。
一行就是一条记录也就是一条数据信息。
表中字段对应的数据能干啥？
（1）记录信息返回给前端。

• 前端直接展示信息
• 前端通过返回的数据存不存在来进行操作控制

（2）通过改变字段的值来进行操作控制