Linux:MySQL（三）单表操作

关系型数据库的常见组件

- 数据库：database
- 表：table
- 行：row
- 列：column
- 索引：index
- 视图：view
- 用户：user
- 权限：privilege
- 存储过程：procedure
- 存储函数：function
- 触发器：trigger
- 事件调度器：event scheduler，任务计划

SQL语言的兴起与语法标准

- 20世纪70年代，IBM开发出SQL，用于DB2
- 1981年，IBM推出SQL/DS数据库
- SQL作为关系型数据库所使用的标准语言，最初是基于IBM的实现在1986年被批准的。1987年，“国际标准化组织(ISO)”把ANSI(美国国家标准化组织) SQL作为国际标准。
- 业内标准微软和Sybase的T-SQL(已经没落没有了），Oracle的PL/SQL
- SQL：ANSI SQL 版本
  SQL-1986，SQL-1989，SQL-1992，SQL-1999，SQL-2003， SQL-2008，SQL-2011

SQL语言规范:

- 在数据库系统中，SQL语句不区分大小写(建议用大写)
- SQL语句可单行或多行书写，以";"结尾
- 关键词不能跨多行或简写
- 用空格和缩进来提高语句的可读性
- 子句通常位于独立行，便于编辑，提高可读性

注释:

SQL标准：
	/*注释内容*/   多行注释
	-- 注释内容    单行注释，注意有空格

MySQL注释：
	#

命名规则：

必须以字母开头
可包括数字和三个特殊字符（# _ $）
不要使用MySQL的保留字 (select等不要用）
同一database(Schema)下的对象不能同名  例：表和表，表和视图不能同名

SQL语句分类：

DDL: Data Defination Language 数据定义语言
CREATE，DROP，ALTER

DML: Data Manipulation Language 数据操纵语言
INSERT，DELETE，UPDATE

DCL：Data Control Language 数据控制语言
GRANT，REVOKE，COMMIT，ROLLBACK

DQL：Data Query Language 数据查询语言
SELECT

SQL语句构成：

Keyword组成clause,多条clause组成语句

示例：
SELECT *             SELECT子句
FROM products        FROM子句
WHERE price>400      WHERE子句

说明：一组SQL语句，由三个子句构成，SELECT,FROM和WHERE是关键字

数据类型：

1、数据类型：数据长什么样，数据需要多少空间来存放
2、系统内置数据类型和用户定义数据类型
3、MySql支持多种列类型：数值类型，日期/时间类型，字符串(字符)类型
https://dev.mysql.com/doc/refman/5.5/en/data-types.html
4、选择正确的数据类型对于获得高性能至关重要
三大原则：
1)更小的通常更好，尽量使用可正确存储数据的最小数据类型
2)简单就好，简单数据类型的操作通常需要更少的CPU周期
3)尽量避免NULL，包含为NULL的列，对MySQL更难优化

一、整型

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值视为真

二、浮点型(float和double)，近似值

float(m,d)   单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)  双精度浮点型16位精度(8字节) m总个数，d小数位

设一个字段定义为float(6,3)，如果插入一个数123.45678,
实际数据库里存的是123.457，但总个数还以实际为准，即6位

三、定点数

在数据库中存放的是精确值,存为十进制
decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位

MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。
例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：
小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。
float使用4个字节存储。double占用8个字节

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal
——例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

四、字符串(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

五、二进制数据：BLOB

•BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，
而Blob是以二进制方式存储，不分大小写
•BLOB存储的数据只能整体读出
•TEXT可以指定字符集，BLOB不用指定字符集

六、日期时间类型

•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 无符号  （添加之后全部是正数，没有复数了就）

数据库操作：

一、 创建数据库：

mysql>CREATE database db1 
CHARACTER set 'utf8mb4' 
COLLATE 'utf8mb4_general_ci';

二、删除数据库

DROP DATABASE db1;

三、查看支持所有字符集：

SHOW CHARACTER SET;

库字符集所在的文件
[root@dushan7 ~]#cd /var/lib/mysql/db1/
[root@dushan7 db1]#cat db.opt 
default-character-set=uft8mb4
default-collation=utf8mb4_general_ci

四、查看支持所有排序规则：

SHOW COLLATION;

库字排序规则所在的文件
[root@dushan7 ~]#cd /var/lib/mysql/db1/
[root@dushan7 db1]#cat db.opt 
default-character-set=uft8mb4
default-collation=utf8mb4_general_ci

五、获取命令使用帮助：

 HELP CREATE; 

六、查看数据库列表：

SHOW DATABASES;

表操作：

一、查看所有的引擎：

SHOW ENGINES;

二、查看表：

SHOW TABLES; 
SHOW TABLES FROM hellodb;

三、查看表结构：

DESC hellodb.students;

四、删除表：

DROP TABLE students;

五、查看表创建命令:

SHOW CREATE TABLE students;

六、查看表状态：

SHOW TABLE STATUS LIKE 'students';

show table status like 'students'\G;
添加\G可以对齐表

七、查看库中所有表状态：

SHOW TABLE STATUS FROM hellodb\G;

创建表：

一、通过查询现存表创建

直接插入查询数据 （复制hellodb库的students表到db1库）
mysql [db1]>
CREATE TABLE newstudents 
SELECT * 
FROM hellodb.students;

不插入数据（添加一个条件过滤where 1=0）
MariaDB [db1]> 
CREATE TABLE newstudents 
SELECT * 
FROM hellodb.students 
WHERE 1 = 0;

二、 通过复制现存的表的表结构创建，但不复制数据 用LIKE

MariaDB [db1]> 
CREATE TABLE newstudents 
LIKE hellodb.students;

三、直接创建表

CREATE TABLE
STUDENTS
	(id int unsigned auto_increment primary key,
	name varchar (50) not null,
	sex enum('f','m'),
	age tinyint unsigned default 20);

复合主键：
CREATE TABLE 
STUDENTS2
	(id int UNSIGNED NOT NULL,
	name VARCHAR(20) NOT NULL,
	age tinyint UNSIGNED,
	PRIMARY KEY(id,name));

修改表操作：

一、添加字段：

ALTER TABLE students 
ADD mobile char(11) 
AFTER name;

ALTER TABLE students 
ADD gender ENUM('m','f')；

二、修改字段属性

ALTER TABLE students
MODIFY mobile int(12);

三、修改字段名

ALTER TABLE students
CHANGE mobile phone char(8);

ALTER TABLE students 
CHANGE COLUMN mobile phone char(8);

四、删除字段：

ALTER TABLE students
DROP phone;

ALTER TABLE students
DROP COLUMN phone;

五、修改表名

ALTER TABLE students RENAME st;

六、添加索引

ALTER TABLE students ADD INDEX(age);

七、查看表上的索引：

show indexes from db1.students\G;

八、删除索引

ALTER TABLE students drop index age;

九、清空表

TRUNCATE TABLE students; 

DML语句 INSERT 插入数据

MariaDB [db1]> desc students;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(50)         | NO   |     | NULL    |                |
| mobile | char(11)            | YES  |     | NULL    |                |
| sex    | enum('f','m')       | YES  |     | NULL    |                |
| age    | tinyint(3) unsigned | YES  | MUL | 20      |                |
+--------+---------------------+------+-----+---------+----------------+
MariaDB [db1]> 
insert into 
students(name,mobile,sex,age)
values('dushan','13522115251','m',30) ;
字符串varchar,字符串char注意加引号  数字tinyint不用加引号

MariaDB [db1]>
insert into
students (mobile,name,sex,age)
values('18500465370','liuying','f',31);
添加顺序可以不一样，但是赋值注意要对应前面

MariaDB [db1]>
insert into 
students(mobile,name,sex,age)
values('527800051','dudou','m',3),('331956294','duwen','m',0);
可同时添加多条记录

MariaDB [db1]> select * from students;
+----+---------+-------------+------+------+
| id | name    | mobile      | sex  | age  |
+----+---------+-------------+------+------+
|  1 | dushan  | 13522115251 | m    |   30 |
|  2 | liuying | 18500465370 | f    |   31 |
|  3 | dudou   | 527800051   | m    |    3 |
|  4 | duwen   | 331956294   | m    |    0 |
+----+---------+-------------+------+------+

MariaDB [db2]> 
insert into 
students (name,mobile,sex,age) 
select name,mobile,sex,age 
from 
db1.students;
从db1库中的表students中的数据插入到db2库中的students表中


可以用另外一种语法添加：
MariaDB [db2]>
insert into students set name='达达',sex='m';

DML语句 UPDATE … SET 修改表内容

语法:UPDATE 表名 SET 修改数据 where 条件

注意：一定要有限制条件，否则将修改所有行的指定字段

限制条件：WHERE、LIMIT

update students 
set sex='m';
没加条件，表内sex将全部改成m，一定要加条件

update students 
set sex='f' 
where id=1;
添加条件，id为1的sex改为f

Mysql 选项：-U|–safe-updates| --i-am-a-dummy

mysql -uroot -pdushan -U
*进入时添加-U，可进入安全更新模式

vim /etc/my.cnf.d/mysql-clients.cnf
*写入配置文件，以免发生批量修改 mysql语句块底下添加
[mysql]
safe-updates    

可打印查看，启动mysql自动补一个--safe-updates
[root@dushan7 ~]#mysql --print-defaults    
mysql would have been started with the following arguments:
--safe-updates 

DML语句 DELETE FROM 删除表内容

语法:DELETE FROM 表名 WHERE 条件

注意：一定要有限制条件，否则将清空表中的所有数据(可先排序再指定删除的行)

限制条件：WHERE、LIMIT

delete from students;
删除所有表记录，同update原理一样，要加条件

delete from students
order by age 
limit 1;
排序删除年龄最小的一条记录

MariaDB [db2]>
delete from students 
where name='duwen' 
order by age 
limit 1;
删除名字叫杜文的

DQL语句 SELECT

一、别名：

select stuid as ID，name 姓名 ,age 年龄 
from students;

可添加as，也可不加as

二、WHERE子句：指明过滤条件以实现“选择”的功能：

过滤条件：布尔型表达式
算术操作符：+, -, *, /, %
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=

1）指明范围：    WHERE..BETWEEN .. AND ..
select * from students 
where birth 
between '1988-1-1 00:00:00' and '1998-12-31 00:00:00';
查询时间格式1988年到1998年之间

2）指明选择条件： WHERE..IN 
select * from hellodb.students 
where classid 
in (1,2,3);
在classid的1,2,3班的列出

3)指明不是=not null：WHERE IS NOT NULL
select * from hellodb.students 
where teacherid 
is null；

三、DISTINCT 去除重复列

SELECT DISTINCT gender FROM students;

四、LIKE: 模糊搜索

select * 
from students 
where name like 's%';
搜索s开头的

% 任意长度的任意字符，用左前缀匹配s%, 不要%s匹配
_ 任意单个字符
RLIKE：正则表达式，索引失效，**不建议使用**
REGEXP：匹配字符串可用正则表达式书写模式，同上
逻辑操作符：NOT、AND、OR、XOR

五、GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算

注意：分组select后必须是2组内容，第一组是分组字段的本身classid，第二组必须是聚合（汇总）函数
avg(), max(), min(), count(), sum()

select classid,count(*) 
from students 
group by ClassID;
每个班有多少人

select classid,min(age) 
from students 
group by ClassID;
每个班年龄最小的

select classid,avg(age) 
from students 
group by classid;
每个班的平均年龄

select classid,gender,count(*) 
from students 
group by classid,gender;
每班的男女各有多少个

六、GROUP BY…HAVING:对分组聚合运算后的结果指定过滤条件

注意：分组前加条件用where,分组后加条件用having

select classid,avg(age) 
from students 
where gender = 'm' 
group by classid 
having classid in (1,2);
先找出男生平均年龄，后过滤1,2班

七、ORDER BY: 根据指定的字段对查询结果进行排序

升序：ASC  对于数字默认就是升序
降序：DESC

select * 
from students 
order by age;

select * 
from students 
order by age desc;

八、LIMIT ：对查询的结果进行输出行数数量限制

select * 
from students 
order by age 
limit 3；
只显示3个

select * 
from students 
order by age 
limit 2，3；
跳过前两个之后，显示3个

对查询结果中的数据请求施加“锁”

FOR UPDATE: 写锁，独占或排它锁，只有一个读和写
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读