SQL语句简介及练习
SQL语言的兴起与语法标准
20世纪70年代,IBM开发出SQL,用于DB2
1981年,IBM推出SQL/DS数据库
业内标准微软和Sybase的T-SQL,Oracle的PL/SQL
SQL作为关系型数据库所使用的标准语言,最初是基于IBM的实现在1986年被批准的。1987年,"国际标准化组织(ISO)"把ANSI(美国国家标准化组织) SQL作为国际标准。
SQL:ANSI SQL
SQL-86, SQL-89, SQL-92, SQL-99, SQL-03
SQL语言规范
在数据库系统中,SQL语句不区分大小写(建议用大写)
但字符串常量区分大小写
SQL语句可单行或多行书写,以";"结尾
关键词不能跨多行或简写
用空格和缩进来提高语句的可读性
子句通常位于独立行,便于编辑,提高可读性
注释:
SQL标准:
/*注释内容*/ 多行注释
-- 注释内容 单行注释,注意有空格
MySQL注释:
除了SQL标准外还可以用#
数据库对象
数据库的组件(对象):
数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
命名规则:
必须以字母开头
可包括数字和三个特殊字符(# _ $,#和$尽量别用)
不要使用MySQL的保留字
同一database(Schema)下的对象不能同名,即使是不同类型的对象
SQL语句分类
SQL语句分类:
DDL: Data Defination Language
CREATE, DROP, ALTER
DML: Data Manipulation Language
INSERT, DELETE, UPDATE
DCL:Data Control Language
GRANT, REVOKE
DQL:Data Query Language
SELECT
SQL语句构成
SQL语句构成:
Keyword组成clause(子句)
多条clause组成语句
示例:
SELECT * SELECT子句
FROM products FROM子句
WHERE price>400 WHERE子句
说明:此为一组SQL语句,由三个子句构成,SELECT,FROM和WHERE是关键字
数据库操作
创建数据库:
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'; #创建数据库
CHARACTER SET 'character set name' #指定字符集
COLLATE 'collate name' #指定排序方式
删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
查看支持所有字符集:SHOW CHARACTER SET;
查看支持所有排序规则:SHOW COLLATION;
获取命令使用帮助:
mysql> HELP KEYWORD;
例:HELP CREATE DATABASE;
看到基本用法及帮助文档的链接,可以去看
URL: http://dev.mysql.com/doc/refman/5.5/en/create-database.html
查看数据库列表:
mysql> SHOW DATABASES;
[root@centos7 ~]#cat /var/lib/mysql/db1/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
表
表:二维关系
设计表:遵循规范
定义:字段,索引
字段:字段名,字段数据类型,修改符
约束,索引:应该创建在经常用作查询条件的字段上
创建表
创建表:CREATE TABLE
(1) 直接创建
(2) 通过查询现存表创建;新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options]
[partition_options] select_statement
(3) 通过复制现存的表的表结构创建,但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
注意:
Storage Engine是指表类型,也即在表创建时指明其使用的存储引擎,同一库中不同表可以使用不同的存储引擎
同一个库中表建议要使用同一种存储引擎类型
创建表
CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修饰符, col2 type2 修饰符, ...) #[IF NOT EXISTS]字段在脚本中一般要加
字段信息
•col type1
•PRIMARY KEY(col1,...)
•INDEX(col1, ...)
•UNIQUE KEY(col1, ...)
表选项:
•ENGINE [=] engine_name
SHOW ENGINES;查看支持的engine类型
•ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
获取帮助:mysql> HELP CREATE TABLE;
表操作
查看所有的引擎:SHOW ENGINES
查看表:SHOW TABLES [FROM db_name]
查看表结构:DESC [db_name.]tb_name
删除表:DROP TABLE [IF EXISTS] tb_name
查看表创建命令:SHOW CREATE TABLE tbl_name
查看表状态:SHOW TABLE STATUS LIKE 'tbl_name'
查看库中所有表状态:SHOW TABLE STATUS FROM db_name
数据类型
数据类型:
数据长什么样?
数据需要多少空间来存放?
系统内置数据类型和用户定义数据类型
MySql支持多种列类型:
数值类型
日期/时间类型
字符串(字符)类型
https://dev.mysql.com/doc/refman/5.5/en/data-types.html
选择正确的数据类型对于获得高性能至关重要,三大原则:
更小的通常更好,尽量使用可正确存储数据的最小数据类型
简单就好,简单数据类型的操作通常需要更少的CPU周期
尽量避免NULL,包含为NULL的列,对MySQL更难优化(可填个默认值)
数据类型
数据类型
1、整型
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(+-9.22*10的18次方)
取值范围如果加了unsigned,则最大值翻倍,如tinyint unsigned的取值范围为(0~255)
int(m)里的m是表示SELECT查询结果集中的显示宽度,并不影响实际的取值范围,规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,Int(1)和Int(20)是相同的
BOOL,BOOLEAN:布尔型,是TINYINT(1)的同义词。zero值被视为假。非zero值视为真
2、浮点型(float和double),近似值
float(m,d) 单精度浮点型 8位精度(4字节) m总个数,d小数位
double(m,d) 双精度浮点型16位精度(8字节) m总个数,d小数位
设一个字段定义为float(6,3),如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以实际为准,即6位
3、定点数
在数据库中存放的是精确值,存为十进制
decimal(m,d) 参数m<65 是总个数,d<30且 d
MySQL5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。例如,decimal(18,9)小数点两边将各存储9个数字,一共使用9个字节:小数点前的数字用4个字节,小数点后的数字用4个字节,小数点本身占1个字节
浮点类型在存储同样范围的值时,通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节
因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用decimal--例如存储财务数据。但在数据量比较大的时候,可以考虑使用bigint代替decimal
4、字符串(char,varchar,_text)
char(n) 固定长度,最多255个字符
varchar(n)可变长度,最多65535个字符
tinytext 可变长度,最多255个字符
text 可变长度,最多65535个字符
mediumtext 可变长度,最多2的24次方-1个字符
longtext 可变长度,最多2的32次方-1个字符
BINARY(M) 固定长度,可存二进制或字符,长度为0-M字节
VARBINARY(M) 可变长度,可存二进制或字符,允许长度为0-M字节
内建类型:ENUM枚举, SET集合
char和varchar:
•1.char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格,varchar不限于此。
•2.char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar是存入的实际字符数+1个字节(n< n>255),所以varchar(4),存入3个字符将占用4个字节。
•3.char类型的字符串检索速度要比varchar类型的快
varchar和text:
•1.varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n< n>255),text是实际字符数+2个字节。
•2.text类型不能有默认值
•3.varchar可直接创建索引,text创建索引要指定前多少个字符。varchar查询速度快于text
5.二进制数据:BLOB
•BLOB和text存储方式不同,TEXT以文本方式存储,英文存储区分大小写,而Blob是以二进制方式存储,不分大小写
•BLOB存储的数据只能整体读出
•TEXT可以指定字符集,BLOB不用指定字符集
6.日期时间类型
•date 日期 '2008-12-2'
•time 时间 '12:25:36'
•datetime 日期时间 '2008-12-2 22:06:44'
•timestamp 自动存储记录修改时间
•YEAR(2), YEAR(4):年份
timestamp字段里的时间数据会随其他字段修改的时候自动刷新,这个数据类型的字段可以存放这条记录最后被修改的时间
修饰符
所有类型:
•NULL 数据列可包含NULL值
•NOT NULL 数据列不允许包含NULL值
•DEFAULT 默认值
•PRIMARY KEY 主键
•UNIQUE KEY 唯一键
•CHARACTER SET name 指定一个字符集
数值型
•AUTO_INCREMENT 自动递增,适用于整数类型
•UNSIGNED 无符号
示例
CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR(20)NOT NULL,age tinyint UNSIGNED);DESC students;CREATE TABLE students2 (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));
表操作
DROP TABLE [IF EXISTS] 'tbl_name'; [CASCADE](级联删除)
ALTER TABLE 'tbl_name'
字段:
添加字段:add
ADD col1 data_type [FIRST|AFTER col_name]
删除字段:drop
修改字段:
alter(默认值), change(字段名), modify(字段属性)
索引:
添加索引:add index
删除索引: drop index
表选项
修改:
查看表上的索引:SHOW INDEXES FROM [db_name.]tbl_name;
查看帮助:Help ALTER TABLE
修改表示例
ALTER TABLE students RENAME s1;ALTER TABLE s1 ADD phone varchar(11) AFTER name;ALTER TABLE s1 MODIFY phone int;ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);ALTER TABLE s1 DROP COLUMN mobile;Help ALTER TABLE 查看帮助
修改表示例
ALTER TABLE students ADD gender ENUM('m','f')(enum单选/枚举,set多选)ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;ALTER TABLE students ADD UNIQUE KEY(name);ALTER TABLE students ADD INDEX(age);DESC students;SHOW INDEXES FROM students;ALTER TABLE students DROP age;
DML语句
DML:INSERT, DELETE, UPDATE, SELECT
INSERT:
一次插入一行或多行数据
语法
INSERT [L OW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE 如果重复更新之
col_name=expr
[, col_name=expr] ... ]
简化写法:
INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)
DML语句
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
DML语句
UPDATE:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
注意:一定要有限制条件,否则将修改所有行的指定字段
限制条件:
WHERE
LIMIT
Mysql 选项:--safe-updates| --i-am-a-dummy|-U
DML语句
DELETE:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
可先排序再指定删除的行数
注意:一定要有限制条件,否则将清空表中的所有数据
限制条件:
WHERE
LIMIT
TRUNCATE TABLE tbl_name; 清空表
DQL语句
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]
SELECT
字段显示可以使用别名:
col1 AS alias1, col2 AS alias2, ...(AS可不写)
WHERE子句:指明过滤条件以实现"选择"的功能:
过滤条件:布尔型表达式
算术操作符:+, -, *, /, %
比较操作符:=, !=, <>, <=, >, >=, <, <=
BETWEEN min_num AND max_num
IN (element1, element2, ...)
IS NULL
IS NOT NULL
SELECT
DISTINCT 去除重复列
SELECT DISTINCT gender FROM students;
LIKE:
%: 任意长度的任意字符
_:任意单个字符
RLIKE:正则表达式,索引失效,不建议使用
REGEXP:匹配字符串可用正则表达式书写模式,同上
逻辑操作符:
NOT
AND
OR
XOR
SELECT
GROUP:根据指定的条件把查询结果进行"分组"以用于做"聚合"运算
avg(), max(), min(), count(), sum()
建议:一旦用分组,前边select只需要写用以分组的字段,和统计的函数,写别的字段没有意义
HAVING: 对分组聚合运算后的结果指定过滤条件(在分组前做过滤用where)
ORDER BY: 根据指定的字段对查询结果进行排序
升序:ASC
降序:DESC
示例:
MariaDB [db1]> select * from students order by score; 顺序,NULL在前
MariaDB [db1]> select * from students order by -score desc; 顺序,但NULL在后
LIMIT [[offset,]row_count]:对查询的结果进行输出行数数量限制
对查询结果中的数据请求施加"锁"
FOR UPDATE: 写锁,独占或排它锁,只有一个读和写
LOCK IN SHARE MODE: 读锁,共享锁,同时多个读(但只能有一个写)
示例
DESC students;INSERT INTO students VALUES(1,'tom','m'),(2,'alice','f');INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');SELECT * FROM students WHERE id < 3;SELECT * FROM students WHERE gender='m';SELECT * FROM students WHERE gender IS NULL;SELECT * FROM students WHERE gender IS NOT NULL;SELECT * FROM students ORDER BY name DESC LIMIT 2;SELECT * FROM students ORDER BY name DESC LIMIT 1,2;SELECT * FROM students WHERE id >=2 and id <=4SELECT * FROM students WHERE BETWEEN 2 AND 4SELECT * FROM students WHERE name LIKE 't%'SELECT * FROM students WHERE name RLIKE '.*[lo].*';SELECT id stuid,name as stuname FROM students
练习
练习数据库下载链接:https://pan.baidu.com/s/11Qk9yI9lx6oZVCGXhvQ6PQ
下载其中的hellodb_innodb.sql,输命令mysql < hellodb_innodb.sql生成一个hellodb数据库:
[root@centos7 ~]#mysql #连接mariadbMariaDB [(none)]> use hellodb #使用hellodb数据库MariaDB [hellodb]> show tables; #共有7张表
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
M