mysql 应用

数据类型、表属性、字符集
1、 数据类型：
作用 保证数据的准确性和标准性。
种类

字符类型
char(11) ：
定长 的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。
varchar(11):
变长的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。
enum(‘bj’,‘tj’,‘sh’)：
枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。

时间类型
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
unique key ：唯一键
列值不能重复
unsigned ：无符号
针对数字列，非负数。
其他属性:
key :索引
可以在某列上建立索引，来优化查询

二进制类型

列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
unique key ：唯一键
列值不能重复
unsigned ：无符号
针对数字列，非负数。
其他属性:
key :索引
可以在某列上建立索引，来优化查询

二进制类型

2、表属性
列属性
约束(一般建表时添加):
primary key ：主键约束
设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。
not null ：非空约束
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
unique key ：唯一键
列值不能重复
unsigned ：无符号
针对数字列，非负数。

其他属性:
key :索引
可以在某列上建立索引，来优化查询,一般是根据需要后添加
default :默认值
列中，没有录入值时，会自动使用default的值填充
auto_increment:自增长
针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）
**comment ** : 注释
表的属性
存储引擎:
InnoDB（默认的）
字符集和排序规则:
utf8
utf8mb4
3、字符集和校对规则
字符集
utf8
utf8mb4
校对规则（排序规则）
大小写是否敏感

DDL应用
创建数据库
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> drop database db;

 查询库相关信息
show databases；
show create database db；

表定义
创建
create table stu(
列1  属性（数据类型、约束、其他属性） ，
列2  属性，
列3  属性
)

建表
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来填充。

删除(生产中禁用命令)
drop table t1;

修改
1在stu表中添加qq列
DESC stu;
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL UNIQUE COMMENT 'qq号';
2在sname后加微信列
ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE  COMMENT '微信号' AFTER sname ;
3在id列前加一个新列num
ALTER TABLE stu ADD num INT NOT NULL COMMENT '数字' FIRST;
DESC stu;
4把刚才添加的列删掉(危险)
ALTER TABLE stu DROP num;
5修改sname数据类型的属性
ALTER TABLE stu MODIFY sname VARCHAR(128)  NOT NULL ;

表属性查询
use school
show tables；
desc stu;
show create table stu；
CREATE TABLE ceshi LIKE stu;


对表中的数据行进行增、删、改
--- 最标准的insert语句
INSERT INTO stu(id,sname,sage,sage,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;

---update
DESC stu;
SELECT * FROM stu;
UPDATE stu SET sname='zhao4' WHERE id=2;
注意：update语句必须要加where。

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


DQL应用(select )
-- 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;


单表子句-from
SELECT 列1,列2 FROM 表
SELECT  *  FROM 表
例子:
-- 查询stu中所有的数据(不要对大表进行操作)
SELECT * FROM stu ;

world            ===>世界
city             ===>城市
country          ===>国家
countrylanguage  ===>国家语言

city:城市表
DESC city;
ID :         城市ID
NAME :       城市名
CountryCode: 国家代码，比如中国CHN 美国USA
District :   区域
Population : 人口

SHOW CREATE TABLE city;
SELECT * FROM city WHERE id<10;


where配合等值查询

例子:
-- 查询中国(CHN)所有城市信息
SELECT * FROM city WHERE countrycode='CHN';
-- 查询北京市的信息
SELECT * FROM city WHERE NAME='peking';
-- 查询甘肃省所有城市信息
SELECT * FROM city WHERE district='gansu';

where配合比较操作符(> < >= <= <>)
例子:
-- 查询世界上少于100人的城市
SELECT * FROM city WHERE population<100;

where配合逻辑运算符(and or )
例子:
-- 中国人口数量大于500w的城市
SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;

-- 中国或美国城市信息
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';

where配合模糊查询
例子:
-- 查询省的名字前面带guang开头的
SELECT * FROM city WHERE district LIKE 'guang%';  

where配合in语句
-- 中国或美国城市信息
SELECT * FROM city WHERE countrycode IN ('CHN' ,'USA');

where配合between and
例子:
-- 查询世界上人口数量大于100w小于200w的城市信息
SELECT * FROM city  WHERE population >1000000 AND population <2000000;
SELECT * FROM city  WHERE population BETWEEN 1000000 AND 2000000;