重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
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无符号 示例 表操作 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 修改表示例 修改表示例 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:读锁，共享锁，同时多个读（但只能有一个写） 示例 练习 练习数据库下载链接：https://pan.baidu.com/s/11Qk9yI9lx6oZVCGXhvQ6PQ 下载其中的hellodb_innodb.sql，输命令mysql < hellodb_innodb.sql生成一个hellodb数据库： +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-------------------+ 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));
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;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 <=4
SELECT * FROM students WHERE BETWEEN 2 AND 4
SELECT * FROM students WHERE name LIKE 't%'
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid,name as stuname FROM students
[root@centos7 ~]#mysql #连接mariadb
MariaDB [(none)]> use hellodb #使用hellodb数据库
MariaDB [hellodb]> show tables; #共有7张表
M
分享标题：SQL语句简介及练习
转载源于：http://cqcxhl.com/article/jsppch.html