SQL入门

总览

基础

什么是SQL: 一种面向数据库的编程语言

什么是DATABASE: 一堆行和列组成表集合

什么是TABLES: 由行列组成的数据集, 如Excel

注意: SQL不区别大小写, 必须由分号结束,中间无所谓有多个空格.

SQL DML 和 DDL

数据操作语言DML - 查询和更新指令构成了 SQL 的 DML 部分：
- SELECT - 从数据库表中获取数据
- UPDATE - 更新数据库表中的数据
- DELETE - 从数据库表中删除数据
- INSERT INTO - 向数据库表中插入数据
数据定义语言 DDL
- CREATE DATABASE - 创建新数据库
- ALTER DATABASE - 修改数据库
- CREATE TABLE - 创建新表
- ALTER TABLE - 变更（改变）数据库表
- DROP TABLE - 删除表
- CREATE INDEX - 创建索引（搜索键）
- DROP INDEX - 删除索引

设置初始密码

主要方法

1 2	$ mysql -uroot -p #输入查看到的密码 mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

方法一

1、使用空的初始密码登录mysql账号：
mysql -uroot -p
 
2、修改root密码：
SET PASSWORD = PASSWORD('123456');

方法二

1、使用空的初始密码登录mysql账号：
mysql -uroot -p
 
2、修改root密码：
mysqladmin -u root password 'new-password'
格式：mysqladmin -u用户名 -p旧密码 password 新密码

方法三

1、使用空的初始密码登录mysql账号：
mysql-uroot -p
 
2、修改root密码：
mysql> update user set Password=password("123456") where User='root';

CentOS7中mysql密码规则

mysql> select @@validate_password_policy;
+----------------------------+
| @@validate_password_policy |
+----------------------------+
| MEDIUM                     |
+----------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)
# 设置密码检查
set global validate_password_policy=0;
set global validate_password_mixed_case_count=0;
set global validate_password_number_count=6;
set global validate_password_special_char_count=0;
set global validate_password_length=6;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
flush privileges;

安装

window host: C:\Windows\System32\drivers\etc

配置文件:

[client]
port=3306
default-character-set=utf8

[mysqld]
# 跳过密码
skip-grant-tables
bind-address=node0
port=3306
# 数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
character-set-server=utf8

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

查看密码:

1
2
3

tail /var/log/mysqld.log
# 初始化设置密码
mysql> SET PASSWORD = PASSWORD('123456');

如果没有完全移除mysql,

1
2
3

sudo apt remove mysql-* //ubuntu
dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P //全选yes
apt install mysql-client mysql-server

安装

wget 'https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm'
rpm -ivh mysql57-community-release-el7-11.noarch.rpm
yum update
yum repolist all | grep mysql
yum install mysql-community-server
#权限
chown mysql:mysql -R /var/lib/mysql
mysqld --initialize --explicit_defaults_for_timestamp
systemctl start mysqld

SET PASSWORD FOR 'root'@'node0' = PASSWORD('123456');
UPDATE user SET password = password('123456') WHERE User = 'root';

启动失败:

[root@node0 lib]# systemctl restart mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

[root@node0 lib]#  tail /var/log/mysqld.log
2019-01-20T05:39:34.170130Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2019-01-20T05:39:34.170135Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2019-01-20T05:39:34.170141Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2019-01-20T05:39:34.170146Z 0 [Note] Shutting down plugin 'InnoDB'
2019-01-20T05:39:34.175937Z 0 [Note] InnoDB: FTS optimize thread exiting.
2019-01-20T05:39:34.176201Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-01-20T05:39:34.176540Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190120 13:39:34
2019-01-20T05:39:34.176638Z 0 [Note] InnoDB: Starting shutdown...
2019-01-20T05:39:34.278234Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2019-01-20T05:39:34.280058Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 190120 13:39:34

修改服务器时间

$ mysql_tzinfo_to_sql tz_dir
$ mysql_tzinfo_to_sql tz_file tz_name
$ mysql_tzinfo_to_sql --leap tz_file
$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
$ mysql_tzinfo_to_sql tz_file tz_name | mysql -u root mysql
$ mysql_tzinfo_to_sql --leap tz_file | mysql -u root mysql

ubuntu不需要密码登录

use mysql;
 
update user set authentication_string=PASSWORD("123456") where user='root';
 
update user set plugin="mysql_native_password";
 
flush privileges;
 
quit;
 
/etc/init.d/mysql restart;
 
mysql -u root -p 密码;

设置权限

1 2	GRANT ALL ON . TO root@'%' IDENTIFIED BY '123456'; FLUSH PRIVILEGES;

警告:

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation. |

ALTER USER 'root'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
show grants for current_user();

操作

从头开始,必须要有一个数据库,然后建一张表

创建数据库

1 2	mysql> CREATE DATABASE test; Query OK, 1 row affected (0.01 sec)

显示数据库

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| test               |
+--------------------+
8 rows in set (0.00 sec)
mysql> use test
Database changed

可以看到刚才创建的test已经创建

删除数据库

1 2	mysql> DROP DATABASE test; Query OK, 31 rows affected, 2 warnings (0.17 sec)

创建表

mysql> CREATE TABLE IF NOT EXISTS user(
           user_id INT UNSIGNED AUTO_INCREMENT,
           nickname VARCHAR(10),
           phone VARCHAR(11),
           PRIMARY KEY ( user_id )
       )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

显示所有的表和详细

mysql> show tables;
+--------------------+
| Tables_in_test     |
+--------------------+
| article            |
| user               |
+--------------------+
4 rows in set (0.00 sec)

mysql> desc user;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| user_id  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| nickname | varchar(10)      | YES  |     | NULL    |                |
| phone    | varchar(11)      | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

导入和到处数据

导入数据county_code.txt, 使用”,”分割

1	mysql> LOAD DATA LOCAL INFILE 'county_code.txt' INTO TABLE test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

数据类型

数值类型

类型	大小(字节)	范围(有符号)	范围(无符号)	用途
TINYINT	1	(-128，127)	(0，255)	小整数值
SMALLINT	2	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

基本操作

删除表: DROP TABLE user

查看数据

mysql> SELECT * FROM user;
+---------+----------+-------------+
| user_id | nickname | phone       |
+---------+----------+-------------+
|       1 | xyxj     | 18888888888 |
+---------+----------+-------------+
1 row in set (0.00 sec)

创建表

CREATE TABLE IF NOT EXISTS person(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10),
    age TINYINT UNSIGNED
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> CREATE TABLE IF NOT EXISTS person(
    ->     id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ->     name VARCHAR(10),
    ->     age TINYINT UNSIGNED
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+---------------------+
| Tables_in_web       |
+---------------------+
| person              |
| test                |
| weather_county_code |
+---------------------+
3 rows in set (0.00 sec)

mysql> desc persion
    -> ;
ERROR 1146 (42S02): Table 'web.persion' doesn't exist
mysql> desc person;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)         | YES  |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

插入

INSERT INTO person VALUES (1,'XY',20);
INSERT INTO person VALUES (2,'XY3',23);
INSERT INTO person VALUES (3,'XY3',23);
INSERT INTO person VALUES (4,'XY2',23);
INSERT INTO person VALUES (5,'XY',23);
INSERT INTO person VALUES (6,'XY3',28);

查询

mysql> select * from person;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | XY   |   20 |
|  2 | XY3  |   23 |
|  3 | XY3  |   23 |
+----+------+------+

distict - 返回唯一不同的值

1 2	//SELECT DISTINCT 列名称 FROM 表名称 SELECT DISTINCT age FROM person;

WHERE条件

语法:

1	SELECT 列名称 FROM 表名称 WHERE 列运算符值

运算符

操作符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式

// select age is 23
mysql> SELECT * FROM person WHERE age>20;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | XY3  |   23 |
|  3 | XY3  |   23 |
+----+------+------+
2 rows in set (0.00 sec)

多条件

AND 和 OR 运算符: AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

mysql> select * from person where age>20 and name='XY3';
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | XY3  |   23 |
|  3 | XY3  |   23 |
|  6 | XY3  |   28 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> select * from person where age>20 or name='XY3';
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | XY3  |   23 |
|  3 | XY3  |   23 |
|  4 | XY2  |   23 |
|  5 | XY   |   23 |
|  6 | XY3  |   28 |
+----+------+------+
5 rows in set (0.00 sec)

ORDER BY 排序

mysql> select name, age from person order by age;
+------+------+
| name | age  |
+------+------+
| XY   |   20 |
| XY3  |   23 |
| XY3  |   23 |
| XY2  |   23 |
| XY   |   23 |
| XY3  |   28 |
+------+------+
6 rows in set, 4 warnings (0.00 sec)

mysql> select name, age from person order by age,name;
+------+------+
| name | age  |
+------+------+
| XY   |   20 |
| XY   |   23 |
| XY2  |   23 |
| XY3  |   23 |
| XY3  |   23 |
| XY3  |   28 |
+------+------+
6 rows in set (0.00 sec)

排序 DESC倒序, 默认正序

mysql> select name, age from person order by age desc;
+------+------+
| name | age  |
+------+------+
| XY3  |   28 |
| XY3  |   23 |
| XY3  |   23 |
| XY2  |   23 |
| XY   |   23 |
| XY   |   20 |
+------+------+
6 rows in set (0.00 sec)

更新 update

1 2	//UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 update person set name = 'XY' where age='30';

删除

1
2
3

//DELETE FROM 表名称 WHERE 列名称 = 值
mysql> delete from person where age = 23;
Query OK, 4 rows affected (0.00 sec)

函数Functions

1	SELECT function(列) FROM 表

MS Access中函数

函数	描述
AVG(column)	返回某列的平均值
COUNT(column)	返回某列的行数（不包括 NULL 值）
COUNT(*)	返回被选行数
FIRST(column)	返回在指定的域中第一个记录的值
LAST(column)	返回在指定的域中最后一个记录的值
MAX(column)	返回某列的最高值
MIN(column)	返回某列的最低值
STDEV(column)
STDEVP(column)
SUM(column)	返回某列的总和
VAR(column)
VARP(column)

在 SQL Server 中的合计函数

函数	描述
AVG(column)	返回某列的平均值
BINARY_CHECKSUM
CHECKSUM
CHECKSUM_AGG
COUNT(column)	返回某列的行数（不包括NULL值）
COUNT(*)	返回被选行数
COUNT(DISTINCT column)	返回相异结果的数目
FIRST(column)	返回在指定的域中第一个记录的值（SQLServer2000 不支持）
LAST(column)	返回在指定的域中最后一个记录的值（SQLServer2000 不支持）
MAX(column)	返回某列的最高值
MIN(column)	返回某列的最低值
STDEV(column)
STDEVP(column)
SUM(column)	返回某列的总和
VAR(column)
VARP(column)

MS Access 中的 Scalar 函数

函数	描述
UCASE(c)	将某个域转换为大写
LCASE(c)	将某个域转换为小写
MID(c,start[,end])	从某个文本域提取字符
LEN(c)	返回某个文本域的长度
INSTR(c,char)	返回在某个文本域中指定字符的数值位置
LEFT(c,number_of_char)	返回某个被请求的文本域的左侧部分
RIGHT(c,number_of_char)	返回某个被请求的文本域的右侧部分
ROUND(c,decimals)	对某个数值域进行指定小数位数的四舍五入
MOD(x,y)	返回除法操作的余数
NOW()	返回当前的系统日期
FORMAT(c,format)	改变某个域的显示方式
DATEDIFF(d,date1,date2)	用于执行日期计算

mysql> select * from person;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | XY   |   20 |
|  6 | XY3  |   28 |
+----+------+------+
2 rows in set (0.00 sec)

//平均值
mysql> select avg(age) from person;
+----------+
| avg(age) |
+----------+
|  24.0000 |
+----------+

mysql> select count(age) from person;
+------------+
| count(age) |
+------------+
|          2 |
+------------+

分组 group by

mysql> select * from person;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | XY   |   20 |
|  2 | XY   |   30 |
|  6 | XY3  |   28 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> select name, sum(age) from person group by name;
+------+----------+
| name | sum(age) |
+------+----------+
| XY   |       50 |
| XY3  |       28 |
+------+----------+
2 rows in set (0.00 sec)

函数 where功能 -在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

mysql> select name, sum(age) from person group by name having sum(age) < 32;
+------+----------+
| name | sum(age) |
+------+----------+
| XY3  |       28 |
+------+----------+
1 row in set (0.00 sec)

UCASE 函数把字段的值转换为大写。

LCASE 函数将字段转换为小写

指定数字小数范围-ROUND 函数用于把数值字段舍入为指定的小数位数。

1	SELECT ROUND(column_name,decimals) FROM table_name

获取时间

mysql> select id, name, age, now() from person;
+----+------+------+---------------------+
| id | name | age  | now()               |
+----+------+------+---------------------+
|  1 | XY   |   20 | 2019-02-21 19:59:51 |
|  2 | XY   |   30 | 2019-02-21 19:59:51 |
|  6 | XY3  |   28 | 2019-02-21 19:59:51 |
+----+------+------+---------------------+
3 rows in set (0.00 sec)

FORMAT() 格式化