mysql-入门教程

use wzsmysql;  # 选择要操作的数据库
show databases; -- 列出所有数据库列表
/*
选择数据库之后
展示当前库所有的表
*/
show tables;
show columns from employee_tbl; #展示表结构
show index from user; #展示表索引
show table status from wzsmysql;# 显示数据库 wzsmysql 中所有表的信息
show table status from wzsmysql where name like "%user%"; #显示表user的信息

create database test1; #创建数据库
drop database test1;#删除数据库

#数值类型 tinyint,smallint,mediumint,int/integer,bigint,float,double,decimal
#日期和时间类型 date,time,year,datetime,timestamp
#字符串类型 char,varchar,tinyblob,tinytext,blob,text,mediumblob,mediumtext,longblob,longtext

#mysql中" ' "和 " ` "的区别
#linux下不区分
#单引号( ' )或双引号主要用于字符串的引用符号；反勾号(‘`’) 主要用于数据库、表、索引、列和别名

SELECT @@sql_mode; #查询当前模式
set session sql_mode = "STRICT_TRANS_TABLES"; #设置当前模式

#创建表
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT, #AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
  `runoob_title` VARCHAR(100) NOT NULL,
  `runoob_author` VARCHAR(40) NOT NULL,
  `submission_date` DATE,
   PRIMARY KEY (`runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8; #ENGINE 设置存储引擎，CHARSET 设置编码。
#UNSIGNED 将数字类型无符号化，如果使用unsigne并且在where子句后出现两列相减值小于0((a-b)<0)，在查询时，linux上的Mysql会将负数转换成unsigned后再进行查询( #(-2576+4294967295+1)>86374,  (-105849+4294967295+1)>86374 )。

drop table runoob_tbl; #删除表
#如果添加过主键自增（PRINARY KEY AUTO_INCREMENT）第一列在增加数据的时候，可以写为0或者null，第一列数据就自动递增。
insert into runoob_tbl(`runoob_id`,`runoob_title`,`runoob_author`,`submission_date`) values (null,'mysql入门到精通','周伯通',now());
#插入多条数据
insert into runoob_tbl(`runoob_id`,`runoob_title`,`runoob_author`,`submission_date`) values (null,'mysql入门到精通-1','周伯通1',now()),(0,'mysql入门到精通-2','周伯通-2',now());
select * from runoob_tbl; #查询表数据

select * from runoob_tbl ;
select * from runoob_tbl where runoob_id = '1';
select  runoob_title,runoob_author from runoob_tbl where runoob_id='1';
select * from runoob_tbl where runoob_id='1' and runoob_title='mysql入门到精通';

#MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
insert into runoob_tbl(`runoob_id`,`runoob_title`,`runoob_author`,`submission_date`) values (null,'MySql入门到精通','周伯通',now());
select * from runoob_tbl where runoob_title='MYsQl入门到精通'; #result = 1
select * from runoob_tbl where binary runoob_title='MYsQl入门到精通'; #result = 0

#执行顺序 select –>where –> group by–> having–>order by
#统计每个部门成绩大于89的员工总数量
select 部门,count(*) from 用户表 where 成绩>89 group by 部门;
#查询每个部门成绩大于89并且人数大于1的部门，并按照人数降序排列
select 部门,count(*) from 用户表 where 成绩>89 group by 部门 having count >1 order by count desc;

update runoob_tbl set runoob_title = 'update_入门到精通' where runoob_id in ('1','3');
update runoob_tbl set runoob_title = 'muti_update_入门到精通' ,runoob_author='muti_update_周伯通' where runoob_id in ('2','5');
#当我们需要将字段中的特定字符串批量修改为其他字符串时
update runoob_tbl set runoob_title=replace(runoob_title,'mysql','redis') where runoob_id in ('8','9');

show columns from runoob_tbl;
# runoob_id设置为UNSIGNED，runoob_id = 2或者runoob_id = '2'都可以删除
delete from runoob_tbl where runoob_id = 2;

#drop，delete，truncate 的区别：
#1、drop是删除表包括表结构；delete,truncate是删除表数据，delete是根据条件删除表数据，truncate是把表里面的数据全部清空。打个比方：delete 是单杀，truncate 是团灭，drop 是把手机摔了。
#2、delete删除完毕没有提交事务还可以回滚，truncate删除后马上生效不能回滚。打个比方：delete是微信发消息说分手，后悔还可以撤回消息；truncate是打电话说分手，对方直接听到，不可挽回。
#3、执行速度drop > truncate > delete

select * from runoob_tbl where runoob_title like '%mysql%';
#l	ike 匹配/模糊匹配，会与 % 和 _ 结合使用
#	'%a'     //以a结尾的数据
#	'a%'     //以a开头的数据
#	'%a%'    //含有a的数据
#	'_a_'    //三位且中间字母是a的
#	'_a'     //两位且结尾字母是a的
#	'a_'     //两位且开头字母是a的

# 在 where like 的条件查询中，SQL 提供了四种匹配方式。
#	%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
# _：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
# []：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
# [^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
# 查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。

#使用正则表达式
select * from runoob_tbl where runoob_title regexp '^m.*通$';

# UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。默认情况下 UNION操作符已经删除了重复数据，UNION [ALL | DISTINCT] 。
CREATE TABLE IF NOT EXISTS `websites` (
	id INT UNSIGNED auto_increment,
	NAME VARCHAR ( 50 ),
	url VARCHAR ( 50 ),
	alexa VARCHAR ( 50 ),
	country VARCHAR ( 50 ) ,
	PRIMARY KEY (`id` )
	) ENGINE = INNODB DEFAULT CHARSET = utf8;

select * from websites;
insert into websites values (null,'Google','https://www.google.cm/','1 ','USA');
insert into websites values (null,'淘宝','https://www.taobao.com/','13','CN');
insert into websites values (null,'菜鸟教程','http://www.runoob.com/','4689','CN');
insert into websites values (null,'微博','http://weibo.com/','20','CN');
insert into websites values (null,'Facebook','https://www.facebook.com/','3','USA');
insert into websites values (null,'stackoverflow','http://stackoverflow.com/','0','IND');

select * from apps;
CREATE TABLE IF NOT EXISTS `apps` (
	id INT UNSIGNED auto_increment,
	app_name   VARCHAR ( 50 ),
	url VARCHAR ( 50 ),
	country VARCHAR ( 50 ),
	PRIMARY KEY (`id` )
	) ENGINE = INNODB DEFAULT CHARSET = utf8;

insert into apps values (null,'QQ APP','http://im.qq.com/','CN');
insert into apps values (null,'微博 APP','http://weibo.com/','CN');
insert into apps values (null,'淘宝 APP','https://www.taobao.com/','CN');


#从 "Websites" 和 "apps" 表中选取所有不同的country（只有不同的值）
select country from websites union distinct select country from apps;
#使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country（也有重复的值）：
select country from websites union all select country from apps;
#UNION ALL 从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据（也有重复的值）：
select * from websites where country='CN' union all select * from apps where country='CN' ; #1222 - The used SELECT statements have a different number of columns
select NAME, url,country from websites where country='CN' union all select app_name, url,country from apps where country='CN' 

#UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
#UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

#ORDER BY 默认按照升序排列。asc:升序，desc:降序
select * from runoob_tbl  order by runoob_id asc;
select * from runoob_tbl  order by runoob_id desc;

#GROUP BY 语句根据一个或多个列对结果集进行分组
CREATE TABLE IF NOT EXISTS`employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `signin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');

select * from employee_tbl;
#使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录：
select name,count(*) from employee_tbl group by name;
#我们将以上的数据表按名字进行分组，再统计每个人登录的次数：
select name,sum(signin) as signin_count from employee_tbl group by name;
#我们将以上的数据表按名字进行分组，再统计每个人登录的次数和登录总数：
#WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
select name,sum(signin) as signin_count from employee_tbl group by name with rollup;
#其中记录 NULL 表示所有人的登录次数。
#coalesce(a,b) 如果a!=null,则选择a；如果a==null,则选择b。
select coalesce(name,'总数') ,sum(signin) as signin_count from employee_tbl group by name with rollup;

#JOIN 按照功能大致分为如下三类：
#INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)。
#LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
#RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

select * from websites;
select * from apps;
/*
mysql> SELECT * FROM tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程  | 10           |
| RUNOOB.COM    | 20           |
| Google        | 22           |
+---------------+--------------+
3 rows in set (0.01 sec)
 
mysql> SELECT * from runoob_tbl;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1         | 学习 PHP    | 菜鸟教程  | 2017-04-12      |
| 2         | 学习 MySQL  | 菜鸟教程  | 2017-04-12      |
| 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
| 5         | 学习 C      | FK            | 2017-04-05      |
+-----------+---------------+---------------+-----------------+
*/
#INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)。
#INNER JOIN取两个表的交际
select a.runoob_id, a.runoob_author, b.runoob_count from tcount_tbl a inner join  runoob_tbl b on a.runoob_author=b.runoob_author;
select a.runoob_id, a.runoob_author, b.runoob_count from tcount_tbl a join  runoob_tbl b on a.runoob_author=b.runoob_author;
select a.runoob_id, a.runoob_author, b.runoob_count   from tcount_tbl a,runoob_tbl b where  a.runoob_author=b.runoob_author;
#LEFT JOIN
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
#RIGHT JOIN
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

#NULL 值处理
#IS NULL: 当列的值是 NULL,此运算符返回 true。
#IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
#<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。
#关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

/*
root@host# mysql -u root -p password;
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> create table runoob_test_tbl
    -> (
    -> runoob_author varchar(40) NOT NULL,
    -> runoob_count  INT
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('RUNOOB', 20);
mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('菜鸟教程', NULL);
mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('Google', NULL);
mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('FK', 20);
 
mysql> SELECT * from runoob_test_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB        | 20           |
| 菜鸟教程  | NULL         |
| Google        | NULL         |
| FK            | 20           |
+---------------+--------------+
*/
select * from runoob_test_tbl where runoob_count is null;
select * from runoob_test_tbl where runoob_count is not null;

#正则表达式
#REGEXP 操作符来进行正则表达式匹配。
/*
模式	描述
^		匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$		匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.	  	匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式。
[...]	    字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...]		负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
p1|p2|p3	匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
*			匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+		匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n}		n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m}		m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。
*/

#事务
/*
一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
1.原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
2.一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
3.隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
4.持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
*/
#MYSQL 事务处理主要有两种方法：
/*
1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
*/
	
	#删除，添加或修改表字段
create table if not exists testalter_tbl(
id int,
username varchar(50),
address varchar(50)
);
show columns from testalter_tbl_re;
ALTER TABLE testalter_tbl RENAME TO testalter_tbl_re;
ALTER TABLE testalter_tbl DROP username; #如果数据表中只剩余一个字段则无法使用DROP来删除字段。
ALTER TABLE testalter_tbl ADD username VARCHAR(50) ;
ALTER TABLE testalter_tbl modify username VARCHAR(250) ;
ALTER TABLE testalter_tbl change username c_username VARCHAR(100);
ALTER TABLE testalter_tbl ALTER c_username SET DEFAULT 1000;
ALTER TABLE testalter_tbl ALTER c_username DROP DEFAULT;
alter table tableName drop foreign key keyName; #删除外键约束：keyName是外键别名

#索引
#索引可以大大提高MySQL的检索速度，同时却会降低更新表的速度。
# 创建索引原则 https://www.cnblogs.com/niuben/p/11250230.html
/*
1、 对于查询频率高的字段创建索引；
2、 对排序、分组、联合查询频率高的字段创建索引； 
3、 索引的数目不宜太多
4、若在实际中，需要将多个列设置索引时，可以采用多列索引
5、选择唯一性索引
6、尽量使用数据量少的索引
7、尽量使用前缀来索引
8、删除不再使用或者很少使用的索引
9、针对多列索引可以入库前程序用hash处理成单个值入库，然后加索引。
*/
/*
index index_name (StudentNo, StudentName, Sex, BirthDate);      #index_name为索引名
创建多列索引，需要遵循BTree类型，即第一列使用时，才启用索引。
在上面的创建语句中，只有mysql语句在使用到StudentNo字段时，索引才会被启用。
*/
CREATE INDEX indexName ON table_name (column_name); #创建索引
ALTER table tableName ADD INDEX indexName(columnName);#修改表结构(添加索引)
DROP INDEX [indexName] ON mytable;  #删除索引的语法


select * from employee_tbl;
show INDEX from employee_tbl; #显示索引信息
drop INDEX employee_tbl_id on employee_tbl ;
CREATE UNIQUE INDEX uni_signin ON employee_tbl(signin) ; 
INSERT INTO `wzsmysql`.`employee_tbl`(`id`, `name`, `date`, `signin`) VALUES (11, '小花', '2021-10-01 17:20:51', '9');

CREATE INDEX name_signin on employee_tbl(name,signin);
explain select * from employee_tbl where name = '小明'; #explain 分析执行过程

CREATE INDEX employee_tbl_id ON employee_tbl (id); #创建索引
explain select * from employee_tbl where id = '1'; #explain 分析执行过程

#唯一索引
#它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
CREATE UNIQUE INDEX indexName ON mytable(username) ; 


#当你创建或设置主键的时候，mysql会自动添加一个与主键对应的唯一索引，不需要再做额外的添加。
/*
主键索引与索引的区别
相同：两个都是索引可以提高数据处理速度，两个的值都是唯一。
不同：
1、主键是一定是唯一性索引，但唯一性索引不一定是主键。
2、一个表可以有多个唯一索引，但主键只能有一个。
3、主键列不能为空，但唯一索引列可以为空。
*/

#临时表
#临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
CREATE TEMPORARY TABLE SalesSummary ( #创建临时表
	product_name VARCHAR(50),
	total_sales VARCHAR(50),
	avg_unit_price VARCHAR(50),
	total_units_sold VARCHAR(50)
);
show tables; #当你使用 SHOW TABLES命令显示数据表列表时，你将无法看到 SalesSummary表。
/*
CREATE TEMPORARY TABLE 临时表名 AS
(
    SELECT *  FROM 旧的表名
    LIMIT 0,10000
);
*/

/*
MySQL 复制表
来给大家区分下mysql复制表的两种方式。
第一、只复制表结构到新表
create table 新表 select * from 旧表 where 1=2
或者
create table 新表 like 旧表 

第二、复制表结构及数据到新表
create table 新表 select * from 旧表;
*/
select * from employee_tbl_2;
show create table employee_tbl_1;
create table employee_tbl_1 like employee_tbl;
create table employee_tbl_2 select * from employee_tbl;

select VERSION(); #服务器版本信息
show STATUS; #服务器状态

#一张数据表只能有一个字段自增主键

#处理重复数据
#INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE INTO 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据
#INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。

#统计user表中first_name 和 last_name的重复记录数
select first_name,last_name,count(*) as count from user group by first_name,last_name having count > 1;
#统计user表中first_name 和 last_name的不重复记录数
select DISTINCT first_name, last_name from user;
select first_name, last_name from user group by first_name, last_name;

#删除重复数据
create table user_temp select first_name, last_name from user group by first_name, last_name;

drop TABLE test_user;
create table if not exists test_user(
	id int UNSIGNED auto_increment,
	first_name varchar(10),
	last_name varchar(10),
	PRIMARY key (id)
)
insert into test_user values(null,'wangzs','zhengs');
insert into test_user values(null,'wangzs1','zhengs1');
insert into test_user values(null,'wangzs1','zhengs1');
insert into test_user values(null,'wangzs2','zhengs2');
insert into test_user values(null,'wangzs5','zhengs2');
insert into test_user values(null,'wangzs3','zhengs3');
insert into test_user values(null,'wangzs4','zhengs2');

select * from test_user;
DELETE from test_user where id not in
#方法1
DELETE from test_user where first_name not in 
	(select t_1.first_name from (select first_name,count(*) as count from test_user group by first_name HAVING count =1) as t_1)
	and id not in
	(select t_2.id from(select min(id) as id,count(*) as count from test_user group by first_name HAVING count >1) as t_2);
	
#方法2
DELETE from test_user where id not in(
		select id from (
				select min(id) as id from test_user group by first_name
				) t 
）

/*
防止SQL注入，我们需要注意以下几个要点：
1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。
*/