mysql中操作表的常用sql

系统管理员 2022-12-28 01:33 171阅读 0赞

## 一.数据库相关 ##

**1.查询所有数据库**  
show databases;

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

**3.查询当前正在使用的数据库名称**  
select database();

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

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

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

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

## 二.数据库表相关操作 ##

**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.查看某个数据库中的所有的数据表**  
show tables;

**4.查看数据表的结构**  
desc 表名;  
describe 表名；  
show columns from 表名;

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

**6.修改表的字符集**  
alter table 表名 character set 字符集名称;

**7.删除表**  
drop table 表名;  
drop table if exists 表名;

**8.字段操作**  
（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;

**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 pet user 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.今天建的索引，明天才能生效呢。**

**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’;