SQL基础应用
更多详细内容请查阅：https://www.jianshu.com/p/08c4b78402ff
1、SQL介绍
结构化查询语言 5.7 以后符合SQL92严格模式 通过sql_mode参数来控制
2、常用SQL分类
DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言
DQL：数据的查询语言
3、数据类型、表属性、字符集
3.1 数据类型
3.1.1 作用
保证数据的准确性和标准性。
3.1.2 种类
数值类型
tinyint ： -128~127 int ：-2^31~2^31-1 说明：手机号是无法存储到int的。一般是使用char类型来存储收集号
字符类型
char(11) ： 定长 的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。 varchar(11): 变长的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。 enum('bj','tj','sh')： 枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。
时间类型
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
unique key ：唯一键
列值不能重复
unsigned ：无符号
针对数字列，非负数。
unique key ：唯一键
列值不能重复
unsigned ：无符号
针对数字列，非负数。
其他属性:
key :索引
可以在某列上建立索引，来优化查询
DATETIME 范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。 TIMESTAMP 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。 timestamp会受到时区的影响
二进制类
3.2 表属性
3.2.1 列属性
约束(一般建表时添加): **primary key** ：主键约束 设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。 **not null** ：非空约束 列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0 **unique key** ：唯一键 列值不能重复 **unsigned** ：无符号 针对数字列，非负数。 其他属性: **key** :索引 可以在某列上建立索引，来优化查询,一般是根据需要后添加 **default** :默认值 列中，没有录入值时，会自动使用default的值填充 **auto_increment**:自增长 针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量） **comment ** : 注释
3.2.2 表的属性
存储引擎:
InnoDB（默认的）
字符集和排序规则:
utf8
utf8mb4
3.3 字符集和校对规则
3.3.1 字符集
utf8
utf8mb4
3.3.2 校对规则（排序规则）
大小写是否敏感
4、DDL应用
4.1 数据定义语言
4.2 库定义
4.2.1 创建
4.2.1 创建数据库
create database school; create schema sch; show charset; show collation; CREATE DATABASE test CHARSET utf8; create database xyz charset utf8mb4 collate utf8mb4_bin; 建库规范： 1.库名不能有大写字母 2.建库要加字符集 3.库名不能有数字开头 4. 库名要和业务相关
建库标志语句
mysql> create database db charset utf8mb4;
mysql> show create database xuexiao;
4.2.2 删除(生产中禁止使用)
mysql> drop database oldboy;
4.2.3 修改
SHOW CREATE DATABASE school;
ALTER DATABASE school CHARSET utf8;
注意：修改字符集，修改后的字符集一定是原字符集的严格超集
4.2.4 查询库相关信息（DQL）
show databases；
show create database oldboy；
4.3 表定义
4.3.1 创建
create table stu(
列1 属性（数据类型、约束、其他属性） ，
列2 属性，
列3 属性
)
4.3.2 建表
USE school; CREATE TABLE stu( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号', sname VARCHAR(255) NOT NULL COMMENT '姓名', sage TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄', sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别' , sfz CHAR(18) NOT NULL UNIQUE COMMENT '身份证', intime TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间' ) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';
建表规范：
1. 表名小写 2. 不能是数字开头 3. 注意字符集和存储引擎 4. 表名和业务有关 5. 选择合适的数据类型 6. 每个列都要有注释 7. 每个列设置为非空，无法保证非空，用0来填充。
4.3.2 删除(生产中禁用命令)
drop table t1;
4.3.3 修改
在stu表中添加qq列
DESC stu; ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL UNIQUE COMMENT 'qq号';
DESC stu;
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL UNIQUE COMMENT 'qq号';
在sname后加微信列
ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname ;
在id列前加一个新列num
ALTER TABLE stu ADD num INT NOT NULL COMMENT '数字' FIRST;
DESC stu;
把刚才添加的列都删掉(危险)
ALTER TABLE stu DROP num;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;
修改sname数据类型的属性
ALTER TABLE stu MODIFY sname VARCHAR(128) NOT NULL ;
将sgender 改为 sg 数据类型改为 CHAR 类型
ALTER TABLE stu CHANGE sgender sg CHAR(1) NOT NULL DEFAULT 'n' ;
DESC stu;4.3.4 表属性查询（DQL）
use school
show tables；
desc stu;
show create table stu；
CREATE TABLE ceshi LIKE stu;
5. DCL应用 ****
grant
revoke
6. DML应用
6.1 作用
对表中的数据行进行增、删、改
6.2 insert
--- 最标准的insert语句 INSERT INTO stu(id,sname,sage,sg,sfz,intime) VALUES (1,'zs',18,'m','123456',NOW()); SELECT * FROM stu; --- 省事的写法 INSERT INTO stu VALUES (2,'ls',18,'m','1234567',NOW()); --- 针对性的录入数据 INSERT INTO stu(sname,sfz) VALUES ('w5','34445788'); --- 同时录入多行数据 INSERT INTO stu(sname,sfz) VALUES ('w55','3444578d8'), ('m6','1212313'), ('aa','123213123123'); SELECT * FROM stu;
6.3 update
DESC stu; SELECT * FROM stu; UPDATE stu SET sname='zhao4' WHERE id=2; 注意：update语句必须要加where。
6.4 delete（危险！！）
DELETE FROM stu WHERE id=3;
全表删除：
DELETE FROM stu
truncate table stu;
区别:
delete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢.
truncate: DDL操作,对与表段中的数据页进行清空,速度快.
伪删除：用update来替代delete，最终保证业务中查不到（select）即可
1.添加状态列 ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 ; SELECT * FROM stu; 2. UPDATE 替代 DELETE UPDATE stu SET state=0 WHERE id=6; 3. 业务语句查询 SELECT * FROM stu WHERE state=1;
7. DQL应用(select )
7.1 单独使用
-- select @@xxx 查看系统参数
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;
-- select 函数();
SELECT NOW(); SELECT DATABASE(); SELECT USER(); SELECT CONCAT("hello world"); SELECT CONCAT(USER,"@",HOST) FROM mysql.user; SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user; https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg
7.2 单表子句-from
SELECT 列1,列2 FROM 表
SELECT * FROM 表
7.3 单表子句-where
SELECT col1,col2 FROM TABLE WHERE colN 条件;
7.3.1 where配合等值查询
例子: -- 查询中国(CHN)所有城市信息 SELECT * FROM city WHERE countrycode='CHN'; -- 查询北京市的信息 SELECT * FROM city WHERE NAME='peking'; -- 查询甘肃省所有城市信息 SELECT * FROM city WHERE district='gansu';
show 命令
show databases; #查看所有数据库 show tables; #查看当前库的所有表 SHOW TABLES FROM #查看某个指定库下的表 show create database world #查看建库语句 show create table world.city #查看建表语句 show grants for root@'localhost' #查看用户的权限信息 show charset； #查看字符集 show collation #查看校对规则 show processlist; #查看数据库连接情况 show index from #表的索引情况 show status #数据库状态查看 SHOW STATUS LIKE '%lock%'; #模糊查询数据库某些状态 SHOW VARIABLES #查看所有配置信息 SHOW variables LIKE '%lock%'; #查看部分配置信息 show engines #查看支持的所有的存储引擎 show engine innodb statusG #查看InnoDB引擎相关的状态信息 show binary logs #列举所有的二进制日志 show master status #查看数据库的日志位置信息 show binlog evnets in #查看二进制日志事件 show slave status G #查看从库状态 SHOW RELAYLOG EVENTS #查看从库relaylog事件信息 desc (show colums from city) #查看表的列定义信息 http://dev.mysql.com/doc/refman/5.7/en/show.html