[TOC]

SQL分类

DDL:数据定义语言
DCL:数据控制语言
DML:数据操作语言
DQL:数据查询语言  #MySQL查询语句类型

DDL语句

库定义

  • 创建
#创建数据库
>create database school;   

#查看创建库的命令内容
>show create database school;

规范

  • 库名大小写统一(Windows不区分大小写,Linux区分大小写)
  • 建库时指定字符集
>create database db charset utf8mb4;  
  • 库名不能以数字开头
  • 见名知其意
  • 删除(禁止删库)
>drop database db;
  • 修改
#修改字符集为utf8(默认为latin1)
>alter database school charset utf8;

#查看字符集
>show charset;

#查看校对集
> show collation;

表定义

表规范

  • 表名小写
  • 不能是数字开头
  • 注意字符集和存储引擎
  • 表名和业务有关
  • 选择合适的数据类型
  • 每列要有注释
  • 每列设置为非空,否则设置默认值(0)

创建表

create table  table_name (

列1  属性(数据类型、约束、其他属性) ,

列2  属性,

列3  属性

)  

#建表举例  
USE db_name;
CREATE TABLE student_info (
id INT PRIMARY KEY NOT NULL  AUTO_INCREMENT COMMENT '学生学号',
sname VARCHAR(64) NOT NULL COMMENT '学生姓名',
gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '学生性别',
phone  CHAR(11) UNIQUE KEY NOT NULL COMMENT '手机号',
age  TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '学生年龄',
time TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
)ENGINE=INNODB CHARSET=utf8mb4;

删除表(禁止使用)

drop table t1;

修改表属性

#在student表的最后加上QQ列
ALTER TABLE school.student ADD qq VARCHAR ( 64 ) UNIQUE KEY NOT NULL COMMENT 'QQ';
DESC stutdent;

#在sname后加微信列
ALTER TABLE school.student ADD Wechat VARCHAR ( 64 ) UNIQUE KEY NOT NULL COMMENT '微信' AFTER sname;
DESC student;

#插入在第一列  
ALTER TABLE school.student ADD num INT FIRST;
DESC student;

#修改sname数据类型
ALTER TABLE student MODIFY sname VARCHAR ( 32 );
DESC student;

#修改sname列名改为sn,数据类型改为varchar(50)
ALTER TABLE student CHANGE sname sn VARCHAR ( 50 );
DESC student;

删除列

#删除num列
ALTER TABLE student DROP num;

表属性查询

#查看表内容
show table_name;

#查看表结构
desc table_name;  

#查看表所有内容的前4行
select * from table_name where id<5; 

#复制source表结构,并创建新的空表
create table source_table_name like new_table_name;

DCL语句

#授予权限
grant

#回收权限
revoke

DML语句

对表中数据进行增、删、改。

INSERT语句

#在学生表插入一行数据
DESC student;
INSERT INTO student(sn,gender,phone,age)
VALUES('zs','m',  '110'  ,18);
SELECT * FROM student;
#省事的插入方法
INSERT INTO student
VALUES(2,'ls','f','190',18,NOW());
SELECT * FROM student;

#一次性录入多行数据
INSERT INTO student(sn,gender,phone,age)
VALUES('w5','f','120',19),('m6','m','119',20),('m66','f','1190',27);
SELECT * FROM student;
INSERT INTO student(sn,phone,age)
VALUES('w55','1200',17);

UPDATE语句

#更改第7行'sn'列为'zhao4'
UPDATE student SET sn='zhao4' WHERE id=7;
注意:update语句必须要加where。

DELETE语句 (删除)

#删除第7行信息
delete from student where id=7;

伪删除:用update来替代delete,最终保证业务中查不到(select)即可。
#添加状态列
alter table student add state tinyint not null default 1;
#使用update替代delete
update student set state=0 where id=6;
select * from xuesheng;
#业务查询时,加入状态判断
select * from student where state=1;

DQL语句

SELECT语句

  • MySQL独有参数
#查看配置文件信息
select @@basedir;
select @@socket;
  • MySQL内置函数
#查看系统当前时间
select now();    

#查看MySQL当前登陆用户
select user();  

#查看当前所在数据库
select database();
  • from
#查看city表中某一列信息 
SELECT NAME,population FROM world.city;

#查看student表的所有信息
select * from  student;
  • where
#查询中国所有城市
select * from city where countrycode='CHN';  

#查询以'US'开头的列(%不可以在最前方)
select * from city where countrycode like 'US%';

#查看中国和美国的城市信息
select * from city where countrycode='CHN' or countrycode='USA';

#同时模糊查找
select * from city where countrycode like 'US%' or countrycode like 'CH%';

group by + 常用聚合函数

作用:根据by后面的条件进行分组,方便统计,by后面跟单列或者多列。

  • 常用聚合函数
max()       #最大值
min()       #最小值
avg()       #平均值
sum()       #总和
count()     #个数