常用sql语句

一、数据库操作相关
1、-- 查看当前数据库
SELECT DATABASE();
– 显示当前时间、用户名、数据库版本
SELECT now(), user(), version();
– 创建库
CREATE DATABASE[ IF NOT EXISTS] 数据库名 数据库选项
数据库选项：
CHARACTER SET charset_name
COLLATE collation_name
– 查看已有库
SHOW DATABASES[ LIKE PATTERN ]
– 查看当前库信息
SHOW CREATE DATABASE +数据库名
– 修改库的选项信息
ALTER DATABASE +库名+ 选项信息
– 删除库
DROP DATABASE[ IF EXISTS] +数据库名：同时删除该数据库相关的目录及其目录内容

eg：
select database();/查询当前数据库名称/
select now(),user(),version() /查询db当前时间、用户、版本号/—如下图

create database ceshi /创建数据库/
show databases /查看已有db库/
show create database ceshi /查看db库信息/-----下图：

drop database ceshi /删除ceshi库/

*备注：drop、delete、truncate区别：drop可删除db库，也可用于删除table表，一旦执行，db库/table表全部删除，db/table表不会再存在结构；delete：常用于删除表数据，对当前表其他数据及表结构没有影响，执行delete后，表结构还是存在。truncate：常用于删除table表，表示将表数据全部清空了，但是字段和表结构还是存在，此时table表是个空表

二、表操作相关
– 创建表
CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [库名.]表名 ( 表的结构定义 )[ 表选项]
备注：1、每个字段必须有数据类型；最后一个字段后不能有逗号
TEMPORARY 临时表，会话结束时表自动消失
2、对于字段的定义：
字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT string ]

– 查看所有表
SHOW TABLES[ LIKE pattern ]
SHOW TABLES FROM 表名
– 查看表机构
SHOW CREATE TABLE 表名 （信息更详细）
SHOW TABLE STATUS [FROM db_name] [LIKE pattern ]
– 修改表
– 修改表本身的选项
ALTER TABLE 表名 表的选项
eg: ALTER TABLE 表名 ENGINE=MYISAM;
– 对表进行重命名
RENAME TABLE 原表名 TO 新表名
RENAME TABLE 原表名 TO 库名.表名 （可将表移动到另一个数据库）
– RENAME可以交换两个表名
– 修改表的字段机构（13.1.2. ALTER TABLE语法）
ALTER TABLE 表名 操作名
– 操作名
ADD +字段+ 字段定义 – 增加字段
AFTER+ 字段名 – 表示增加在该字段名后面
FIRST – 表示增加在第一个
ADD PRIMARY KEY(字段名) – 创建主键
ADD UNIQUE [索引名] (字段名)-- 创建唯一索引
ADD INDEX [索引名] (字段名) – 创建普通索引
DROP[ COLUMN] 字段名 – 删除字段
MODIFY[ COLUMN] 字段名 字段属性 – 支持对字段属性进行修改，不能修改字段名(所有原有属性也需写上)
CHANGE[ COLUMN] 原字段名 新字段名 字段属性 – 支持对字段名修改
DROP PRIMARY KEY – 删除主键(删除主键前需删除其AUTO_INCREMENT属性)
DROP INDEX 索引名 – 删除索引
DROP FOREIGN KEY 外键 – 删除外键
– 删除表
DROP TABLE[ IF EXISTS] 表名 …
– 清空表数据
TRUNCATE [TABLE] 表名
– 复制表结构
CREATE TABLE 表名 LIKE 要复制的表名
– 复制表结构和数据
CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名
– 检查表是否有错误
CHECK TABLE tbl_name [, tbl_name] … [option] …
– 优化表
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …
– 修复表
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] … [QUICK] [EXTENDED] [USE_FRM]
– 分析表
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …

基本sql–建表常用例子：
1、创建表
create table user (
id int (255),
name varchar(255),
num int(255),
job varchar(255)
)
表结构如下：若创建时候字段后面不加 not null，创建成功后，字段就会默认为null，就如下图所示

下面是创建表，字段不为空：
create table user1 (
id int (255) not null,
name varchar(255),
num int(255),
job varchar(255)
)
此时可见字段截图如下：可见字段id是不为空的

2、show tables /查看当前db的所有表/
show tables from ceshi /查看ceshi库的表/

3、同样show create table+表名称，也是查看表结构
show table status from ceshi 类似 show create table+表名称

4、
use ceshi/切换到测试库/
rename table user to user0 /将表user重命名为user0/
rename table user0 to ceshi.user0 /将ceshi库中的user0表移动到mysql库中，将一个表从一个库移动到另一库时候，表名称不能更改/
create table user0 like user1 /在当前db一个表user0，结构和user1一样/
create table user2 as select *from user0 /创建一个新表user2,数据和结构复制user0/

5、drop table +表名称：删除表，只能用drop，不能用delete、truncate
6、
alter table user0 add age int default 0 not null /在表user0中增加一个字段age，且默认为0，不为空，int default 0和Not null是字段定义/
alter table user0 drop age /user0表删除字段字段age/
alter table user0 add age int default 0 not null after id /user0表在id后面添加一个字段age，为0，不能为空/
alter table user0 add age int default 0 not null first /user0表新增一个字段，排在第一个位置，默认为0，不能为空/
alter table user0 add primary key(id,age0,…为字段名) 为字段创建主键
alter table user0 drop primary key 删除主键
alter table user0 add unique index(id，age…为字段名) 为字段添加唯一索引
alter table user0 drop INDEX id 删除索引，id为字段名

7、check table user1 检查表是否有误
analyze table user分析表

三、sql数据增删改查
select * from db表 where 字段=value ：查询db表字段=value的全部数据
select *from db表 where 字段 like "%v% "：查询db表，字段value包含v在内的所有数据
select *from db表 where 字段 like "%v "：查询db表，字段value以v结束的所有数据
select * from db 表 where 字段 like “v%” ： 查询db表，字段value以v开始的所有数据
select distinct 字段 from db表 ：查询db表中，根据字段排重的值
select * from db 表 where 字段=values order by 字段 desc : 查询表中字段=values的数据，且按照字段来倒叙排序
select *from db表 where 字段=value order by 字段 asc： 查询表中字段=values的数据，且按照字段来正叙排序
select * from db表 where 字段=value limit +数值N ： 查询表中字段=values的数据，只查询前N条
select * from db表 where 字段名称 between 字段值 and 字段值 （或者用>= <= <>）：查询字段区间值
eg：
select * from user0 where id =4
select * from user0 where name like “%w%”

select *from user0 where name like “w%”

select *from user0 where name like “%s”

比如表中有一个字段是一样的值，要排重，如下图：

要查询name值，且重复要排重，就要用到distinct
select distinct name from user0

select * from user0 order by id desc

select *from user0 order by id asc

下面是个按照倒叙排序，且查询出某些字段值的sql语句（正序一样，将desc改成asc即可）

注意：sql查询结果的字段顺序是按照语句中查询的字段顺序决定的，比如上述sql语句中，查询的字段顺序分别是：id、name、num，这样查询出来的字段顺序也是：id、name、num（可以根据需要来改变字段顺序）

如下图db表中只有3条数据，要查询前1条，此时就要用到limit了，如下图db原数据：

按照语句查询后：查询出了第一条数据，limit后面的数值可以根据具体的业务需要来变化

当然上面的语句可以混合使用：select * from user0 where name=xxx order by id desc limit 2

要查询区间的数据，就要用到between…and… 或者 >= <= <>，如db表中原数据如下图：

要查询id在4和6之间的数据，则

或者用：select *from user0 where id>=4 and id<=6，两个语句查询出来的结果是一样，<>代表不等于，也可以用到具体的sql语句中

下面介绍实现两个表的联合查询：
实现两个表的联合查询，查出左表的所有数据和右边中和左边相等的数据，没有的以null来展示（
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行）
如：表1存在id等字段、表2存在id等字段，表2中字段id2和表1的字段id相同，则用到left join语句如下，查询出来的结果：包含表1的所有字段数据，表2中和表1联结字段相同的值，不相同的以null来展示（同样right join inner join也是这样用）：

select * from 表1
left join 表2
on表1.id=表2.id2

上面语句等同于：

select *from 表1，表2 where 表1.id=表2.id2

– 增
INSERT into +表名 +(字段列表)]+VALUES+ (值列表)[, (值列表), …]
– 如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。
– 可同时插入多条数据记录！
REPLACE 与 INSERT 完全一样，可互换。
INSERT +[INTO] +表名 +SET+ 字段名=值[, 字段名=值, …]
– 查
SELECT 字段列表 FROM 表名[ 其他子句]
– 可来自多个表的多个字段
– 其他子句可以不使用
– 字段列表可以用*代替，表示所有字段
– 删
DELETE FROM 表名[ 删除条件子句]
没有条件子句，则会删除全部
– 改
UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]

eg：
insert into user (id,name,num,job,age)values(4,‘wss’,13,‘wwwww’,25),
(5,‘xaioming’,15,‘zhiye’, 45）：插入多条记录
insert into user set id=6,name=‘xiaohua’,num=14,job=‘gongzuo’,age=26：也可以插入记录
delete from user where id=6：删除一条数据，具体和drop、truncate的区别上面有介绍
update user set num=‘1234’,age=90…(为字段需修改的值) where id=1 ： 多个字段的更新