第一章 SQL的介绍
1.1什么是sql
SQL：Structure Query Language。（结构化查询语言）,通过sql操作数据库(操作数据库,操作表,操作数据)
SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准
各数据库厂商(MySql,oracle,sql server)都支持ISO的SQL标准。
各数据库厂商在标准的基础上做了自己的扩展。 各个数据库自己特定的语法
1.2 sql的分类
Data Definition Language (DDL数据定义语言) 如：操作数据库，操作表
Data Manipulation Language(DML数据操纵语言)，如：对表中的记录操作增删改
Data Query Language(DQL 数据查询语言)，如：对表中数据的查询操作
Data Control Language(DCL 数据控制语言)，如：对用户权限的设置
1.3 MySQL的语法规范和要求
（1）mysql的sql语法不区分大小写
MySQL的关键字和函数名等不区分大小写，但是对于数据值是否区分大小写，和字符集与校对规则有关。
ci（大小写不敏感），cs（大小写敏感），_bin（二元，即比较是基于字符编码的值而与language无关，区分大小写）
（2）命名时：尽量使用26个英文字母大小写，数字0-9，下划线，不要使用其他符号user_id
（3）建议不要使用mysql的关键字等来作为表名、字段名等，如果不小心使用，请在SQL语句中使用`（飘号）引起来
（4）数据库和表名、字段名等对象名中间不要包含空格
（5）同一个mysql软件中，数据库不能同名，同一个库中，表不能重名，同一个表中，字段不能重名
（6）标点符号：
必须成对
必须英文状态下半角输入方式
字符串和日期类型可以使用单引号’’
列的别名可以使用双引号""，给表名取别名不要使用双引号。取别名时as可以省略
如果列的别名没有包含空格，可以省略双引号，如果有空格双引号不能省略。
（7）SQL脚本中如何加注释
单行注释：#注释内容
单行注释：–空格注释内容 其中–后面的空格必须有
多行注释：/* 注释内容 */
#以下两句是一样的，不区分大小写 show databases; SHOW DATABASES; #创建表格 #create table student info(...); #表名错误，因为表名有空格 create table student_info(...); #其中name使用``飘号，因为name和系统关键字或系统函数名等预定义标识符重名了。 CREATE TABLE t_stu( id INT, `name` VARCHAR(20) ); select id as "编号", `name` as "姓名" from t_stu; #起别名时，as都可以省略 select id as 编号, `name` as 姓名 from t_stu; #如果字段别名中没有空格，那么可以省略"" select id as 编 号, `name` as 姓 名 from t_stu; #错误，如果字段别名中有空格，那么不能省略""
第二章-DDL操作数据库
2.1创建数据库(掌握)
- 语法
create database 数据库名 [character set 字符集][collate 校对规则] 注: []意思是可选的意思
字符集(charset)：是一套符号和编码。
- 练习
创建一个day01的数据库（默认字符集)
create database day01;
创建一个day01_2的数据库,指定字符集为gbk(了解)
create database day01_2 character set gbk;
2.2查看所有的数据库
2.2.1查看所有的数据库
- 语法
show databases;
2.2.2查看数据库的定义结构【了解】
- 语法
show create database 数据库名;
- 查看day01这个数据库的定义
show create database day01;
2.3删除数据库
- 语法
drop database 数据库名;
- 删除day01_2数据库
drop database day01_2;
2.4修改数据库【了解】
- 语法
alter database 数据库名 character set 字符集;
- 修改day01这个数据库的字符集(gbk)
alter database day01 character set gbk; • 1
注意：
- 是utf8，不是utf-8
- 不是修改数据库名
2.5其他操作
- 切换数据库, 选定哪一个数据库
use 数据库名; //注意: 在创建表之前一定要指定数据库. use 数据库名
- 练习: 使用day01
use day01; • 1
- 查看正在使用的数据库
select database();
第三章-DDL操作表
3.1 创建表
3.1.1 语法
create table 表名( 列名 类型 [约束], 列名 类型 [约束] ... );
3.1.2 类型
3.1.2.1 数值类型
- 整型系列：xxxInt
int(M)，必须和unsigned zerofill一起使用才有意义
浮点型系列：float,double（或real)
double(M,D)：表示最长为M位，其中小数点后D位
例如：double(5,2)表示的数据范围[-999.99,999.99]，如果超过这个范围会报错。
定点型系列：decimal（底层实际上是使用字符串进行存储）
decimal(M,D)：表示最长为M位，其中小数点后D位
位类型：bit
字节范围是：1-8，值范围是：bit(1)~bit(64)，默认bit(1)
用来存储二进制数。对于位字段，直接使用select命令将不会看到结果。可以使用bit()或hex()函数进行读取。插入bit类型字段时，使用bit()函数转为二进制值再插入，因为二进制码是“01”。
3.1.2.2 日期时间类型
日期时间类型：year, date, datetime, timestamp
注意一下每一种日期时间的表示范围
timestamp和datetime的区别：
timestamp范围比较小
timestamp和时区有关
show variables like ‘time_zone’;
set time_zone = ‘+8:00’;
timestamp受MySQL版本和服务器的SQLMode影响很大
表中的第一个非空的timestamp字段如果插入和更新为NULL则会自动设置为系统时间
3.1.2.3 字符串类型
MySQL中提供了多种对字符数据的存储类型，不同的版本可能有所差异。常见的有：
char,varchar，xxtext，binary，varbinary，xxblob，enum，set等等
字符串类型char,varchar(M)
char如果没有指定宽度，默认为1个字符
varchar(M)，必须指定宽度
binary和varbinary类似于char和varchar，不同的是它们包含二进制字符串，不支持模糊查询之类的。
一般在保存少量字符串的时候，我们会选择char和varchar；而在保存较大文本时，通常会选择使用text或blob系列。blob和text值会引起一些性能问题，特别是在执行了大量的删除操作时，会在数据表中留下很大的“空洞”，为了提高性能，建议定期时候用optimize table功能对这类表进行碎片整理。可以使用合成的(Synthetic)索引来提高大文本字段的查询性能，如果需要对大文本字段进行模糊查询，MySql提供了前缀索引。但是仍然要在不必要的时候避免检索大型的blob或text值。
enum枚举类型，它的值范围需要在创建表时通过枚举方式显式指定，对于1~255个成员的枚举需要1个字节存储；对于255`65535个成员需要2个字节存储。例如：gender enum(‘男’,‘女’)。如果插入枚举值以外的值，会按第一个值处理。一次只能从枚举值中选择一个。
set集合类型，可以包含0~64个成员。一次可以从集合中选择多个成员。如果选择了1-8个成员的集合，占1个字节，依次占2个，3个。。8个字节。例如：hoppy set(‘吃饭’,‘睡觉’,‘玩游戏’,‘旅游’)，选择时’吃饭,睡觉’或’睡觉,玩游戏,旅游’
3.1.2.4 示例
+----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | eid | int(11) | NO | PRI | NULL | auto_increment | | ename | varchar(20) | NO | | NULL | | | tel | char(11) | NO | | NULL | | | gender | char(1) | YES | | 男 | | | salary | double | YES | | NULL | | | commission_pct | double(3,2) | YES | | NULL | | | birthday | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | job_id | int(11) | YES | | NULL | | | email | varchar(32) | YES | | NULL | | | mid | int(11) | YES | | NULL | | | address | varchar(150) | YES | | NULL | | | native_place | varchar(10) | YES | | NULL | | | did | int(11) | YES | | NULL | | +----------------+--------------+------+-----+---------+----------------+
3.1.3 约束
- 即规则,规矩 限制;
- 作用：保证用户插入的数据保存到数据库中是符合规范的
| 约束 | 约束关键字 | |
| 主键 | primary key | 非空且唯一，并且一张表只能有一个主键 |
| 唯一 | unique | 唯一，当前列不能出现相同的数据 |
| 非空 | not null | 非空，当前列不能为null |
| 默认 | default | 如果当前列没有数据，则指定默认数据 |
约束种类:
not null: 非空 ; eg: username varchar(40) not null username这个列不能有null值
unique:唯一约束, 后面的数据不能和前面重复; eg: cardNo char(18) unique; cardNo 列里面不可以有重复数据
primary key；主键约束(非空+唯一); 一般用在表的id列上面. 一张表基本上都有id列的, id列作为唯一标识的
auto_increment: 自动增长,必须是设置了primary key之后,才可以使用auto_increment
id int primary key auto_increment; id不需要我们自己维护了, 插入数据的时候直接插入null, 自动的增长进行填充进去, 避免重复了.
注意:
- 先设置了primary key 再能设置auto_increment
- 只有当设置了auto_increment 才可以插入null , 否则插入null会报错
id列:
- 给id设置为int类型, 添加主键约束, 自动增长
- 或者给id设置为字符串类型,添加主键约束, 不能设置自动增长
3.1.4练习
- 创建一张学生表(含有id字段,姓名字段不能重复,性别字段不能为空默认值为男. id为主键自动增长)
CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增长 NAME VARCHAR(30) UNIQUE, -- 唯一约束 gender CHAR(1) NOT NULL DEFAULT '男' );
3.2 查看表【了解】
3.2.1查看所有的表
show tables;
3.2.2查看表的定义结构
- 语法
desc 表名； - 练习: 查看student表的定义结构
desc student;
3.3 修改表【掌握，但是不要记忆】
3.3.1语法
- 增加一列
alter table 【数据库名.]表名称 add 【column】 字段名 数据类型; alter table 【数据库名.]表名称 add 【column】 字段名 数据类型 first; alter table 【数据库名.]表名称 add 【column】 字段名 数据类型 after 另一个字段;
修改列的类型约束:alter table 表名 modify 字段 类型 约束 ；
修改列的名称，类型，约束: alter table 表名 change 旧列 新列 类型 约束;
删除一列: alter table 表名 drop 列名;
修改表名 : rename table 旧表名 to 新表名;
3.3.2练习
- 给学生表增加一个grade字段,类型为varchar(20),不能为空
ALTER TABLE student ADD grade VARCHAR(20) NOT NULL;
- 给学生表的gender字段改成int类型，不能为空，默认值为1
alter table student modify gender varchar(20);
- 给学生表的grade字段修改成class字段
ALTER TABLE student CHANGE grade class VARCHAR(20) NOT NULL;
- 把class字段删除
ALTER TABLE student DROP class;
- 把学生表修改成老师表(了解)
RENAME TABLE student TO teacher;
3.4 删除表【掌握】
- 语法
drop table 表名； - 把teacher表删除
drop table teacher;
第四章-DML操作表记录-增删改【重点】
- 准备工作: 创建一张商品表(商品id,商品名称,商品价格,商品数量.)
create table product( pid int primary key auto_increment, pname varchar(40), price double, num int );
4.1 插入记录
4.1.1 语法
- 方式一: 插入指定列, 如果没有把这个列进行列出来, 以null进行自动赋值了.
- eg: 只想插入pname, price , insert into t_product(pname, price) values(‘mac’,18000);
insert into 表名(列,列..) values(值,值..);
注意: 如果没有插入了列设置了非空约束, 会报错的
- 方式二: 插入所有的列,如果哪列不想插入值,则需要赋值为null
insert into 表名 values(值,值....); eg: insert into product values(null,'苹果电脑',18000.0,10); insert into product values(null,'华为5G手机',30000,20); insert into product values(null,'小米手机',1800,30); insert into product values(null,'iPhonex',8000,10); insert into product values(null,'iPhone7',6000,200); insert into product values(null,'iPhone6s',4000,1000); insert into product values(null,'iPhone6',3500,100); insert into product values(null,'iPhone5s',3000,100); insert into product values(null,'方便面',4.5,1000); insert into product values(null,'咖啡',11,200); insert into product values(null,'矿泉水',3,500);
4.2 更新记录
4.2.1语法
update 表名 set 列 =值, 列 =值 [where 条件]
4.2.2练习
- 将所有商品的价格修改为5000元
update product set price = 5000;
- 将商品名是苹果电脑的价格修改为18000元
UPDATE product set price = 18000 WHERE pname = '苹果电脑';
- 将商品名是苹果电脑的价格修改为17000,数量修改为5
UPDATE product set price = 17000,num = 5 WHERE pname = '苹果电脑';
- 将商品名是方便面的商品的价格在原有基础上增加2元
UPDATE product set price = price+2 WHERE pname = '方便面';
4.3 删除记录
4.3.1 delete
根据条件，一条一条数据进行删除
- 语法
delete from 表名 [where 条件] 注意: 删除数据用delete,不用truncate
- 类型
删除表中名称为’苹果电脑’的记录
delete from product where pname = '苹果电脑';
删除价格小于5001的商品记录
delete from product where price < 5001;
删除表中的所有记录(要删除一般不建议使用delete语句，delete语句是一行一行执行，速度过慢)
delete from product;
4.3.2truncate
把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快
truncate table 表;
4.3.3 工作中删除数据
- 物理删除: 真正的删除了, 数据不在, 使用delete就属于物理删除
- 逻辑删除: 没有真正的删除, 数据还在. 搞一个标记, 其实逻辑删除是更新 eg: state 1 启用 0禁用
