SQL语句-入门到精通

一、SQL介绍——基础

1.什么是SQL语句

• SQL，Structured Query Language，中文结构化查询语言，是一种对关系数据库 数据定义和操作语言。

2.SQL分类

• DDL DDL全拼Data Definition Language，数据定义语言 常用关键字:CREATE（创建）、ALTER（修改）、DROP（删除） 查看帮助 :?Data Definition 特点:增删改数据库的库、表、索引等。

• DCL DCL全拼Data Control Language，数据控制语言，常用关键字:GRANT（用户授权）、REVOKE（权限回收）、COMMIT（提交）、 ROLLBACK（回滚）。 执行?Account Management可查相关帮助。

• DML DML全拼Data Manipulation Language，数据操作语言 主要关键字为INSERT（增）、DELETE（删）、UPDATE（改），主要针对数据库 表里数据进行操作。 执行mysql> ? Data Manipulation可查相关帮助。

• DQL DQL全拼Data Query Language，数据查询语言，作用是从表中获取数据。 相关常用保留字有WHERE、ORDER BY、GROUP BY和HAVING。

3.SQL语句实践

3.1 DDL语句管理数据库

• 默认创建数据库，默认字符集为utf8mb4 

create databae rgq(数据库名); 

• 制定数据库字符集和校对规则建库语法

create database rgq character set charset_name collate collation_name;

#例子：
create database A;
create database A default character set utf8 collate utf8_generl_ci;

• 查看字符集和校对规则

show charset;  ##查看支持的字符集和校对规则
show collation;  ##查看支持的校对规则

• 查看建库帮助

? create database

• 根据具体字符集和建库语句创建需要的库

create database rgq;
create set gbk collate gbk_chinese_ci;   ###

• 查看数据库的常用命令（可help show获取帮助）：

show databases;  ##<==查询所有数据库
show databases   like 'rgq';  #<==匹配rgq字符串的内容
show databases like 'rg%' ; #<==%为通配符，表示匹配以rg开头的所有内容。
​
use rgq ;##切换数据库 ==>相当于cd /rgq  进入到指定的数据库。
​
select database();  #查看当前用户所在库pwd.

• 修改数据库

#语法:
ALTER DATABASE [db_name]
      CHARACTER SET charset_name
      [COLLATE collation_name;]
      
#例子：修改字符集为utf8
mysql> alter database rgq charset utf8;

• 删除数据库

drop database rgq;
#注意
1.管理员谨慎使用drop命令。
2.业务用户中，禁止有drop等破坏元数据的权限。
3.数据库名不能用大写字母，不能是关键字，不能使数字开头。
4.创建数据库时应显示设置字符集，8.0可以忽略本条。
CREATE DATABASE rgq CHARACTER SET utf8mb4 COLLATE
utf8mb4_0900_ai_ci;

3.2 DDL语句之管理表

1.表数据类型

• 表的数据类型作用于列上的，用于控制存储数据的"格式"和规范。

a.整型

列类型	存储容量	正数数字范围	负数数字范围	说明
tinyint(微小整型)	1bytes	0-255	-128~127	最大3位数
int	4bytes	0-2^32-1	-2^31~2^31-1	最大10位数
bigint	8bytes	0-2^64-1	-2^63~2^63-1	最大20位数

b.浮点型（小数）

• 除了整数之外，还有浮点数，一般用于对于精度要求很高的业务，例如和钱有关的业务，例如：99.99。 可以使用放大倍数手法把浮点数变成整数存储，例如：99.99放大100倍，为9999，使用在缩小100倍。 提示：在数据库里以整数形式存储,提高读取效率.

c.字符类型

• char(10) 定长，浪费存储空间，作为条件列查询会更快。
• varchar(10) 变长，节省空间

d.时间类型

• 时间类型往往是数据表中的必备类型

列类型	存储容量	说明
timestamp	4字节	从1970-1-1到2038-1-19，秒数，存储用时间戳。
datatime	8字节	从1000-1-1到9999-12-31，显示用时间格式。

PHP程序时间戳转成日期形式函数：date('Y-m-d H:i:s',$row['createtime'])

e.枚举类型

判断，选择：enum('N','Y')

性别：enum('F','M','N')

省份：固定多个简单值的内容。

2.表的约束属性

类型	说明
PRIMARY KEY	简写PK,设置于表主键列，非空且唯一，用于必填项且不能重复。
NOT NULL/NULL	表示列的内容是否非空，空列不利于数据库优化。
UNIQUE KEY	简写UK,表示列的内容唯一，#比如手机号，就不能重复。
FOREING KEY	简写FK,表示表的外键，多个表之间关联用的

• 生产经验：

  • 每张表务必设置一个主键列，建议是数字且自增，学号，身份证，订单编号。

  • 表的每个列尽量设置非空，给默认值。

3.表的其它属性

类型	说明
AUTO_INCREMENT [=] value	设置主键自增长，一般为数字自增长，默认为1.
COMMENT 'string'	对列等信息进行注释
ENGINE [=] engine_name	指定表存储引擎（MyQSL文件系统），默认为innodb
CHARACTER SET charset_name	指定表的字符集，默认为utf8mb4.
COLLATE [=] collation_name	指定表的校对规则
DEFAULT	设定列的默认值，如果是时间列取当前时间。
unsigned	无符号数字（非负数）

4.建表

• 建库

create database A;
use A;

• 在A库创建表stu

create table <表名> (
<字段名1> <类型1> ,
<字段名2> <类型2> ,
…
<字段名n> <类型n>);

#例句
USE oldboy;
CREATE TABLE stu(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(64) NOT NULL COMMENT '姓名',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
gender ENUM('M','F','N') NOT NULL DEFAULT 'N' COMMENT '性别',
telnum CHAR(15) NOT NULL DEFAULT '0' COMMENT '手机号'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';


#查看建表语句
mysql> show create table stu\G
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
  `sname` varchar(64) NOT NULL COMMENT '姓名',
  `age` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `gender` enum('M','F','N') NOT NULL DEFAULT 'N' COMMENT '性别',
  `telnum` char(15) NOT NULL DEFAULT '0' COMMENT '手机号',
  `state` tinyint NOT NULL DEFAULT '1' COMMENT '状态:1存在,0不存在',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表'
1 row in set (0.00 sec)

• 查看表信息

#查看表信息
show tables;  #或show  tables from 库名；
#查看表结构
desc stu;

• 更改表名

方式1
rename table stu to test;
方式2
alter table test rename to stu;

• 增删改表字段

（1）添加列默认到所有列结尾，命令语法为：
alter table 表名 add 字段 类型 其他;

#1.在stu表中添加addr列，默认是最后一列
alter table stu add addr varchar(100) not null comment '地址'；

#2.在sname列后插入dept列。使用after sname;
alter table stu add dept varchar(32) after sname;

#3.在首列插入qq列。使用first
alter table stu add qq varchar(15) first;

#4.若要同时添加以上两个字段，还可采用如下命令
alter table stu drop dept;
alter table stu drop qq;
alter table stu add dept varchar(32) after sname,add qq varchar(15) first;

#5.修改字段类型的命令如下：
alter table stu modify dept char(64);  #<==将dept数据类型及长度改为char(64).

#6.删除列的命令如下
alter table stu drop qq;
alter table stu drop dept;
alter table stu drop addr;

• 删除表

drop  table stu;

3.2 DML语句之管理表数据

建立A库，创建stu表的完成语句示例，表名为stu。

create database A;
USE A;
CREATE TABLE stu(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学
号',
sname VARCHAR(64) NOT NULL COMMENT '姓名',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
gender ENUM('M','F','N') NOT NULL DEFAULT 'N' COMMENT '性
别',
telnum CHAR(15) NOT NULL DEFAULT '0' COMMENT '手机号'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';

• 往表中插入数据使用INSERT关键字

#第一种：指定列插入
insert  into stu(id,sname,age,gender,telnum)  values(1,'A',28,'M','111');
#注意：数字不加引号，字符加引号

#第二种：按顺序插入，可以不指定列（重点）
insert into stu values(2,'b',25,'F','126');

#第三种：批量插入多行（节省IO）,注意缩进。
INSERT INTO
    stu
values
    (3,'Jack',18,'M','189'),
    (4,'Tim',35,'F','183');
    
#只插入部分列,需要按顺序指定列名,且其他列允许为空,不允许为空有默认值
INSERT INTO stu(id,sname) VALUES(2,'oldgril');



#通过分区插入数据
insert into table a partition(ds='{bizdate}')  ## partition ==>表分区
select a.cons_no,
·············

• 修改表的数据--update

练习1:把名字为Tim的人的年龄改为29 
update stu set age=29 where sname='Tim'; 

练习2:把id为2的行的名字改为girl 
update stu set sname='girl' where id=2; 

练习3:把手机号为189的行,性别改为N? 
update stu set gender='N' where telnum='189'; 

4.修改数据导致的事故案例和解决方案 不加条件执行下面语句: 
update stu set sname='oldboy'; 
恢复：mysql -uroot -poldboy123 oldboy 

• 删除表的数据