启动数据库
systemctl start mariadb.service #centos7
my.cnf #centos7配置文件
mysqld #windows
my.ini #windows配置文件
数据库管理
1. 更改密码
- 方式1
mysqladmin -u root -p flush-privileges password "newpass";
修改root用户的密码为newpass，如果不指定密码，则会以密文的形式输入，这样更安全。
- 方式2
mysql -u root -p -e "SET PASSWORD FOR 'root'@'127.0.0.1' PASSWORD('new_pwd');" ;
mysqladmin -u root -p flush-privileges ;
先使用root用户修改root用户的127.0.0.1主机登录的密码。 使用set passsword来更新数据库的，需要使用flush-privileges来更新一下特权，使更改生效。
2. 登录数据库
mysql -uroot -p; #然后根据提示输入密码即可
不登录直接操作数据库的方式是mysql -uroot -p -e "指令内容"。
3. 清除数据库指令
mysql -up \c
如若输错指令无法挽回，使用\c来清除之前的输入。
数据库操作
1. 新建数据库
CREATE DATABASE database_name;
CREATE DATABASE rookeryCHARACTER SET latin1 COLLATE latin1_bin; #指定了字符集和校对方式
创建数据库可带参数，不带参数则使用默认参数。
2. 显示当前可用数据库
SHOW DATABASES;
3. 设置默认数据库
use database_name;
进行此操作后，后续操作默认使用此数据库下的表，如需引用其他数据库的表使用"数据库名.表名的方式"。
4. 显示数据库中的表
SHOW TABLES FROM database_name;
SHOW TABLES IN rookery LIKE 'birds%';
显示databse_name中的所有表，若操作前使用了use database_name，则只需使用SHOW TABLES操作。也可使用通配符操作，使用IN+LIKE关键字可以查询到与birds开头的表。
5. 备份数据库
mysqldump -u root -p database_name > /path/databasexx.sql;
注意进行此操作要退出数据库。
6. 还原数据库
mysql -u root -p database_name < /path/databasexx.sql;
一定注意命令是mysql，需注意还原的时候必须数据库必须存在(就算是空的也可以)，否则会还原失败,还原首先会删除掉数据库中的所有数据表，然后根据还原内容中的数据进行还原，之前没有备份过的数据表全部都会丢失，使用时一定要注意。
表操作
1. 新建表
CREATE TABLE table_name (table_id INT,table_name TEXT);
新建表时需要制定表的列名称和类型，也可指定字符集，后边增加。
2. 查看表结构
1. DESCRIBE table_name;
2. SHOW CREATE TABLE table_name;
两种方式都可以，方式1显示的内容较为简单，只包含表的最基本结构。方式2显示的内容较为全面，包含了字符集和校对方式、存储引擎等，还包含默认值设置等信息。可以在结尾使用\G来分行显示数据。
3. 更改表
语法：ALTER TABLE table_name changes;
ALTER TABLE birds ADD COLUMN bird_order INT;
#向表birds中增加一列为bird_order，类型INT
ALTER TABLE birds_new DROP COLUMN wing_id;
#删除birds_new中的wing_id列
ALTER TABLE birds_new
ADD COLUMN wing_id CHAR(2) AFTER family_id;
#在family_id列后边加入wing_id列
ALTER TABLE birds_new ADD COLUMN body_id CHAR(2) AFTER wing_id,
ADD COLUMN bill_id CHAR(2) AFTER body_id,
ADD COLUMN endangered BIT DEFAULT b'1' AFTER bill_id,
CHANGE COLUMN common_name common_name VARCHAR(255);
#添加了多列，并改变了common_name列的属性
ALTER TABLE table_name
ALTER COLUMN column_name SET/DROP DEFAULT 9;
ALTER TABLE birds_new
CHANGE COLUMN endangered conservation_status_id INT DEFAULT 8;
#使用ALTER命令来单独设置/删除默认值，此外还可以通过CHANGE命令来修改column的名称等属性的同时修改一下默认值，默认是都是用DEFAULT来定义的
ALTER TABLE table_name AUTO_INCREMENT=10;
#修改了表table_name的自增值，以后设置了AUTO_CREMENT的列新增的条目将从10开始自增。
使用ALTER TALBE + ADD/DROP/CHANGE/ALTER COLUM + AFTER等不同的组合产生不同的效果。
| 属性值 | 描述 |
|---|---|
| DEFUALT | 列的默认值，建立表格时指定，可以通过ALTER来修改。 |
| AUTO_INCREMENT | 建立表格时指定，但是需要与PRIMARY KEY绑定，只有主键才可自增。 |
| PRIMARY KEY | 主键，一个表格只能有一个主键，并且值是唯一的。 |
4. 表复制
CREATE TABLE test.bird LIKE birds;
INSERT INTO birds_new SELECT * FROM rookery.birds;
先使用LIKE关键字复制表的结构，在使用INSERT INTO + SELECT的方式进行数据复制。
CREATE TABLE test.bird SELECT * FROM rookery.birds;
直接使用 CREATE TABLE + SELECT 的方式来复制表结构和表数据。
5. 表数据查询
5.1 普通查询
SELECT * FROM table_name;
#查询表table_name的全部
SELECT column1,column2 FROM table_name;
#查询表table_name的column1和column2列
5.2 多表查询
SELECT book_id, title, status_name
FROM books JOIN status_names
WHERE status = status_id;
#将books表和status_names表的book_id，title，status_name连接起来，需要满足条件status=status_idbook_id,title分别来自books表，status_name来自stauts_names表，第二行中的JOIN语句代表的是除了books表外的第二个表，第三行status来自books表，status_id来自status_name表
SELECT name1 AS 'NAME',name2
FROM table1,table2
WHERE condition;
#同JOIN语句的功能，不过可以将JOIN换成',' ,并可以同时连接多个表，可是还可使用AS来命名查询结果的表头信息，此例中将name1命名为NAME
可使用*来通配所有数据，即显示所有列数据，也可执行列，注意数据中间使用','来分隔开。数据查询时可使用WHERE来进行条件判断。使用JOIN语句可将两个表格连接起来，这个连接只是在查询的时候连接，并非在数据库中存在物理连接。
5.3 条件查询
SELECT * FROM table_name WHERE table_id=1;
#显示table_id=1的行
SELECT * FROM table_name
WHERE table_id IN(1,2,3,4,5);
#显示table_id符合IN关键字指定的值的行
SELECT common_name AS 'Birds Great and Small'
FROM birds
WHERE common_name REGEXP 'Great|Least'
ORDER BY family_id LIMIT 10;
#使用表达式来限定条件，选取common_name等于Great或者Least的行
REGEXP指令后的内容是不区分大小写的，要区分大小写要在其后加上BINARY关键字。
SELECT common_name AS 'Birds Great and Small'
FROM birds
WHERE common_name LIKE 'Gr%'
ORDER BY family_id LIMIT 10;
#使用LIKE+通配符的方式限定条件，选取common_name等于Gr开头的行
WHERE后的操作符可以换做'!=' '>' '<' 等，还可以用AND操作符来指定多个条件。
5.4 排序查询
5.4.1 一级排序
SELECT * FROM country_codes
ORDER BY country_name LIMIT 3;
#按照ORDER BY指定的键进行排序操作，并显示最开头的三行数据
SELECT * FROM country_codes
ORDER BY country_name DESC LIMIT 3;
#按照ORDER BY指定的键进行倒序排序操作，并显示最开头的三行数据
注意这里的排序只是查询的时候显示，并不是数据库中的实际排序。如果上边的SELECT语句中使用了AS来定义别名，那么ORDER BY语句就要用到别名，否则会报错。
5.4.2 多级排序查询
select Id,Mode,PartitionNo,SrcPath,DstPath,CreateTime FROM TaskInfo
WHERE State=0 AND ACTION=1
ORDER BY Priority,CreateTime;
多级查询在ORDER BY之后使用','分隔，先试用Proiority排序再使用CreateTime排序
5.4.3 指定查询偏移与范围limit
select * from table limit 1,2
#当limit给定两个参数后，第一个参数为偏移量(从0开始)，第二个参数是从这个偏移量开始需要读取多少个数据。
5.5 区分大小写的查询
SELECT FileName,FilePath,CartridgeId FROM FileInfo WHERE binary CONCAT(FilePath,'',FileName)='/L30MTeSt/20191101/example.tar.gz';
在要查询的语句之前加上binary 此种方式可以区分大小写，或者在建立表格的时候指定binary即可。
5.6 合并查询
select
d1.ID,CAST(d1.ID AS CHAR) AS intId, d1.CODE_TYPE, d1.CODE, d1.CODE_IMG, d1.VALUE
from m_dict_code d1
where d1.CODE_TYPE in('USER_TYPE','addSupers')
UNION
select 0,v.ID,v.TYPE,v.`CODE`,v.DESCRIPTION,v.VALUE_STRING from m_variable as v where v.type='tequan'
limit 1,20
使用union进行合并查询，可以合并同一个表查询，也可以是多个表；使用UNION ALL 允许查询结果重复，使用UNION自动合并重复结果。
6. 插入数据
6.1 明确插入
INSERT INTO bird_families
SET scientific_name = 'Rallidae',
order_id = 113;
明确的插入表中指定列的值，使用等号进行赋值，每次只能插入一个值。
6.2 顺序插入
INSERT INTO table_name VALUES('java',1);
INSERT INTO table_name(column1,column2) VALUES(1,2);
INSERT INTO table_name VALUES('PHP',100),('C',101);
插入数据可指定某一列或者多个列，不指定的，默认为所有列，可同时插入多条数据。插入的数据以VALUES修饰。
6.3 插入其他表的数据
INSERT IGNORE INTO bird_families
(scientific_name, brief_description, cornell_bird_order)
SELECT bird_family, examples, bird_order
FROM cornell_birds_families_orders;
# 因scientific_name设置为UNIQUE不允许出现重复，所以说要加入INGNORE忽略错误
# 将表cornell_birds_families_orders中的三项对应插入bird_families中
使用IGNORE关键字，可以忽略插入过程中产生的错误，后期可以使用SHOW WARNINGS来查看之前报错。
6.4 替换数据
REPLACE INTO bird_families
(scientific_name, brief_description, order_id)
VALUES('Viduidae', 'Indigobirds & Whydahs', 128),
('Estrildidae', 'Waxbills, Weaver Finches, & Allies', 128),
('Ploceidae', 'Weavers, Malimbe, & Bishops', 128);
REPLACE的用法和INSERT相同，不过REPLACE遇到重复的时候会进行替换操作，操作原理是删除掉原来的一行所有的数据，然后把语句中给出的数据写入进去，需要注意的是，如果这一行中有其他列有数据，而本次插入数据的时候并没有提供该数据，那么该列的数据会被置为默认值。
7. 更新数据
7.1 更新单个表
UPDATE table_name SET id=3 WHERE id=4;
UPDATE table_name SET id=3,name=‘C’ WHERE id=4;
基本语法为UPDATE ... SET ，实际使用中采用条件判断语句，决定更新的列，如果不使用WHERE语句，则会更新整个数据表。
7.2 更新多个表
UPDATE prize_winners, humans
SET winner_date = NULL,
prize_chosen = NULL,
prize_sent = NULL
WHERE country_id = 'uk'
AND prize_winners.human_id = humans.human_id;
#实际上就是从一个表中查询符合要求的human_id，利用这个id来查找另一个表当中符合要求的数据进行更新。
在多表更新的语句当中不允许出现LIMIT或者ORDER BY语句，如果要使用，需要放到子查询语句当中。
UPDATE prize_winners
SET winner_date = CURDATE()
WHERE winner_date IS NULL
AND human_id IN
(SELECT human_id
FROM humans
WHERE country_id = 'uk'
ORDER BY RAND())
LIMIT 2;
#采用了子查询方式的更新两个表
7.3 处理重复
INSERT INTO humans
(formal_title, name_first, name_last, email_address, better_birders_site)
VALUES('Mr','Barry','Pilson', 'barry@gomail.com', 1),
('Ms','Lexi','Hollar', 'alexandra@mysqlresources.com', 1),
('Mr','Ricky','Adams', 'ricky@gomail.com', 1)
ON DUPLICATE KEY
UPDATE better_birders_site = 2;
#将VALUES指定的内容插入到表中，出现重复的情况下将better_birders_site值更新为2，否则这个值为1
当出现重复的时候，可以使用INSERT IGNORE INTO来忽略，或者使用REPLACE INTO来覆盖，还可以使用的就这这种方式，使用INSERT INTO + ON DUPLICATE KEY UPDTATE的方式标记出表中原有的重复项。
7.4 更新的子查询，查询并修改
需要将表mytest中的ChangeTime不为NULL的行的TaskOrder更新为0
update mytest AS B
JOIN (Select ID,TaskOrder from mytest WHERE ChangeTime IS NOT NULL) AS A
USING(ID) SET B.TaskOrder=0;
值得注意的是：临时的表只能用来查询，不能用来更改，所以要更改就必须要使用原来的表，这就是为什么会用到mystest AS B的原因了。
8. 删除数据
8.1 单表删除
基本语法
DELETE FROM table
[WHERE condition]
[ORDER BY column]
[LIMIT row_count];
DELETE FROM humans
WHERE name_first = 'Elena'
AND name_last = 'Bokova'
AND email_address LIKE '%yahoo.com';
#删除满足条件的数据
8.2 多表删除
基本语法
DELETE FROM table[, table]
USING table[, . . . ]
[WHERE condition];
DELETE FROM humans, prize_winners
USING humans JOIN prize_winners
WHERE name_first = 'Elena'
AND name_last = 'Bokova'
AND email_address LIKE '%yahoo.com'
AND humans.human_id = prize_winners.human_id;
#表humans同prize_winners是有一定关联的表，满足where后条件的情况下删除两个表中的数据
删除多表的情况在于两个表使用某一列作为关联，FROM后的语句代表的是删除哪些表当中的数据，USING后的语句代表的是WHERE后条件使用的表，JOIN同样也可用','替代。最重要的是最后一条，两个表需要满足的条件，有一列的值相等，两表中满足此条件的将会被删除。
#删除t1中满足条件的数据
DELETE t1 FROM t1,t2 WHERE xxx=xxx
#删除t1和t2满足条件的数据
DELETE t1,t2 FROM t1,t2 WHERE xxx=xxx
//,号可以替换为连接符，和查表的方式一样可以替换成任何的连接方式例如
DELETE t1 FROM t1 JOIN t2 ON(xxx=xxx) WHERE xxx=xxx
9. 删除表
DROP TABLE table_name;
10. 备份表
mysqldump -u root -p database_name table_name > /path/xxx.sql;
操作前在系统命令行下，要退出数据库。
11. 恢复表
mysql -uroot -p database_name table_name < /path/xxx.sql;
###########占空##########
12. 重命名/移动表
RENAME TABLE database_name.table1
TO database_name.table2,
database_name.table1 TO database_name1.table1;
#将database_name下的table1重命名为table2，将database_name下的table1移动到database_name下并命名为table1
13. 显示表的索引信息
SHOW INDEX FROM table_name;
索引是和列绑定的，但是两者并不是同一个东西，索引是数据库搜索时用来使用的东西，例如要搜索命令SLELET，那么就会存在这么一个表，记录着书中SELECT出现的位置。
14. 新建索引
ALTER TABLE birdwatchers.humans
ADD INDEX human_names (name_last, name_first);
#为表humans增加一个索引，这个索引是由姓氏和名字组成的，当用户使用姓氏或者名字或者二者组合是就可以用的这个索引
新建的索引名字可以自定义，在使用SELECT语句进行查询的时候，可以在前端加入EXPLAIN关键字，就会显示出数据库是根据哪一索引找到的数据。
15. 统计结果计数和分组
SELECT orders.scientific_name AS 'Order',
families.scientific_name AS 'Family',
COUNT(*) AS 'Number of Birds'
FROM birds, bird_families AS families, bird_orders AS orders
WHERE birds.family_id = families.family_id
AND families.order_id = orders.order_id
AND orders.scientific_name = 'Pelecaniformes'
GROUP BY Family;
#以Family为分组，统计每个分组满足条件的数量
表连接和子查询
1. 合并结果集
基本语法
SELECT ... FROM ... UNION SELECT ... FROM ...
SELECT 'Pelecanidae' AS 'Family',
COUNT(*) AS 'Species'
FROM birds, bird_families AS families
WHERE birds.family_id = families.family_id
AND families.scientific_name = 'Pelecanidae'
UNION
SELECT 'Ardeidae',
COUNT(*)
FROM birds, bird_families AS families
WHERE birds.family_id = families.family_id
AND families.scientific_name = 'Ardeidae';
#将域名设置为Family，统计出种类为Pelecanidae和Ardeidae的数量
UNION只能用于两个SELECT中间，把两次查询的结果显示在同一张表中，域名是根据第一个SELECT语句后的第一个域名来设定的，如果为这个域名设置了别名，那最后就以别名来显示。 需要注意的是两个查询语句的列数必须是一样的。
2. 表连接
基本语法
SELECT book_id, title, status_name
FROM books JOIN status_names
WHERE status = status_id;
查询时使用JOIN或者','连接两个表或多个表，WHERE语句中指明两个表的关联条件，也可用使用USING和ON的方式，例如：
2.1 连接方式
SELECT book_id, title, status_name
FROM books JOIN status_names
ON(status = status_id);
#使用ON语句替换掉WHERE语句当中的条件
SELECT book_id, title, status_name
FROM books JOIN status_names
USING(status_id);
#使用USING语句替换掉WHERE语句当中的条件，需要注意的是两个表中的列都必须要叫status_id;
2.2 多表连接
SELECT book_id, title, status_name
FROM books
JOIN status_names USING(status_id)
JOIN title_names USING(title_id);
#连接多个表的使用
条件可以任意指定，三个表的连接方式可以是任意 ，可以同一个表连接两个表，也可用两两之间有连接，根据实际情况编写条件。
SELECT common_name AS 'Bird',
conservation_state AS 'Status'
FROM birds
LEFT JOIN conservation_status USING(conservation_status_id)
WHERE common_name LIKE '%Egret%'
ORDER BY Status, Bird;
使用LEFT JOIN代表结果以左表birds为主，遍历出birds表的所有项，来匹配convert_status表，当左表有的项，右表无法匹配的时候，右表的结果会显示为NULL；此外还有RIGHT JOIN，以右表为主，当左表无法满足条件时左表显示结果为NULL；JOIN和INNER JOIN代表内查询，只会找到符合要求的结果。
2.3 更新已连接的表
select concat(first_name,second_name) AS 'NAME' ,location from user left join user_work USING(work_id);
UPDATE user LEFT JOIN user_work USING(work_id) SET work_id=5
WHERE work_id IS NULL;
UPDATE + LEFT JOIN的使用主要是在主表当中的与另一个表的关联值未设置的情况下需要使用到的，上例中user表中的work_id列未设置值，则在查询语句当中会显示NULL，然后使用此更新语句对未设置值的work_id列设置值。
2.4 删除已连接的表
DELETE FROM humans, prize_winners
USING humans LEFT JOIN prize_winners
ON humans.human_id = prize_winners.human_id
WHERE name_first = 'Elena'
AND name_last = 'Bokova'
AND email_address LIKE '%yahoo.com';
在 LEFT JOIN 的情况下，符合筛选条件并且两 表匹配的行自然会被删除，而那些符合筛选条件，但在prize_winners 中找不到对应行的humans 行，也会被删除。这样做可以消除孤立行。
3. 子查询
例子
UPDATE table_1
SET col_5 = 1
WHERE col_id =
SELECT col_id
FROM table_2
WHERE col_1 = value;
SELECT column_a, column_1
FROM table_1
JOIN
(SELECT column_1, column_2
FROM table_2
WHERE column_2 = value) AS derived_table
USING(col_id);
#第一个子查询返回的是一个值，属于WHERE中的子查询，第二个子查询返回的是一个临时的表，这个表由满足条件的两列组成。
第一个例子中的 SELECT 是内部查询，而 UPDATE 则是外部查询（或称主查询）。在第二个例 子中，括号里的 SELECT 是内部查询，括号外的是外部查询。包含子查询的外部查询可以是 SELECT 、 INSERT、 UPDATE、 DELETE、 DO 或者甚至是 SET。不过有个限制：外部查询一般不能 查询或修改内部查询所查的表。（但在 FROM 中用子查询时，没有这样的限制。） 子查询一般都可以使用JOIN语句来替换掉，这取决于个人的编码风格。
3.1 标量子查询
此方式的子查询到最后返回的结果就是一个值。
UPDATE humans
SET membership_type = 'premium',
membership_expiration = DATE_ADD(IFNULL(membership_expiration,
CURDATE()), INTERVAL 1 YEAR)
WHERE human_id =
(SELECT human_id
FROM
(SELECT human_id, COUNT(*) AS sightings, join_date
FROM birdwatchers.bird_sightings
JOIN birdwatchers.humans USING(human_id)
JOIN rookery.birds USING(bird_id)
JOIN rookery.bird_families USING(family_id)
WHERE country_id = 'ru'
AND bird_families.scientific_name = 'Scolopacidae'
GROUP BY human_id) AS derived_1
WHERE sightings > 5
ORDER BY join_date DESC
LIMIT 1);
3.2 列子查询
返回多个值，即返回值可以使用IN（）的形式来访问。
SELECT * FROM
(SELECT common_name AS 'Bird',
families.scientific_name AS 'Family'
FROM birds
JOIN bird_families AS families USING(family_id)
JOIN bird_orders AS orders USING(order_id)
WHERE common_name != ''
AND families.scientific_name IN
(SELECT DISTINCT families.scientific_name AS 'Family'
FROM bird_families AS families
JOIN bird_orders AS orders USING(order_id)
WHERE orders.scientific_name = 'Galliformes'
ORDER BY Family)
ORDER BY RAND()) AS derived_1
GROUP BY (Family);
3.3 行子查询
查询结果返回一行，可以用于INSERT语句按行数据插入。
INSERT INTO bird_sightings
(bird_id, human_id, time_seen, location_gps)
VALUES
(SELECT birds.bird_id, humans.human_id,
date_spotted, gps_coordinates
FROM
(SELECT personal_name, family_name, science_name, date_spotted,
CONCAT(latitude, '; ', longitude) AS gps_coordinates
FROM eastern_birders
JOIN eastern_birders_spottings USING(birder_id)
WHERE
(personal_name, family_name,
science_name, CONCAT(latitude, '; ', longitude) )
NOT IN
(SELECT name_first, name_last, scientific_name, location_gps
FROM humans
JOIN bird_sightings USING(human_id)
JOIN rookery.birds USING(bird_id) ) ) AS derived_1
JOIN humans
ON(personal_name = name_first
AND family_name = name_last)
JOIN rookery.birds
ON(scientific_name = science_name) );
3.4 表子查询
将查询的结果使用AS的方式临时生成一个表，作为SELECT FROM的参数来使用，此方式不能使用外部查询的数据结果。
SELECT family AS 'Bird Family',
COUNT(*) AS 'Number of Birds'
FROM
(SELECT families.scientific_name AS family
FROM birds
JOIN bird_families AS families USING(family_id)
WHERE families.scientific_name IN('Pelecanidae','Ardeidae')) AS derived_1
GROUP BY family;
505
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
