• MySQL复制，约束条件，查询与安全控制

    MySQL之复制

    复制表

    1. 我有一个表
    2. mysql> show tables;
    3. +------------------+
    4. | Tables_in_school |
    5. +------------------+
    6. | student          |
    7. +------------------+
    8.  
    9. mysql> select * from student;
    10. +------+-------+--------+------+----------+-------+
    11. | id   | name  | sec    | age  | address  | phone |
    12. +------+-------+--------+------+----------+-------+
    13. |    2 | jack  | male   |   20 | shanghai |   110 |
    14. |    3 | alice | female |   20 | nanjing  |   110 |
    15. +------+-------+--------+------+----------+-------+
    16.  
    17.  复制这个表的id，name，phone。
    18. mysql> create table ml(select id,name,phone from student);
    19.  
    20. mysql> show tables;
    21. +------------------+
    22. | Tables_in_school |
    23. +------------------+
    24. | ml               |
    25.  
    26.  
    27. 查看表的内容及结构：
    28. mysql> select * from ml;
    29. +------+-------+-------+
    30. | id   | name  | phone |
    31. +------+-------+-------+
    32. |    2 | jack  |   110 |
    33. |    3 | alice |   110 |
    34. +------+-------+-------+
    35. 2 rows in set (0.00 sec)
    36.  
    37. mysql> desc ml;
    38. +-------+-------------+------+-----+---------+-------+
    39. | Field | Type        | Null | Key | Default | Extra |
    40. +-------+-------------+------+-----+---------+-------+
    41. | id    | int(11)     | YES  |     | NULL    |       |
    42. | name  | varchar(10) | YES  |     | NULL    |       |
    43. | phone | int(11)     | YES  |     | NULL    |       |
    44. +-------+-------------+------+-----+---------+-------+
    45. 3 rows in set (0.00 sec)

    语法： create table 新表名（select   *  from  要复制的表名）

    *可以是要复制表里面的字段

    复制表的结构

    1. mysql> create table zx(select * from ml where 4=5);
    2. mysql> show tables;
    3. +------------------+
    4. | Tables_in_school |
    5. +------------------+
    6. | ml               |
    7. |      
    8. | zx               |
    9. +------------------+
    10.  
    11. mysql> select * from zx;
    12. Empty set (0.00 sec) --------由于执行语句where 4=5，不成立，因此只复制表结构 不复制表数据
    13.  
    14. mysql> desc zx;
    15. +-------+-------------+------+-----+---------+-------+
    16. | Field | Type        | Null | Key | Default | Extra |
    17. +-------+-------------+------+-----+---------+-------+
    18. | id    | int(11)     | YES  |     | NULL    |       |
    19. | name  | varchar(10) | YES  |     | NULL    |       |
    20. | phone | int(11)     | YES  |     | NULL    |       |
    21. +-------+-------------+------+-----+---------+-------+
    22. 3 rows in set (0.00 sec)
    1. mysql> create table bs(select * from ml where 2=2);
    2. mysql> show tables;
    3. +------------------+
    4. | Tables_in_school |
    5. +------------------+
    6. | bs               |
    7. | ml               |
    8. | student          |
    9. | zx               |
    10. +------------------+
    11.  
    12. mysql> select * from bs;
    13. +------+-------+-------+
    14. | id   | name  | phone |
    15. +------+-------+-------+
    16. |    2 | jack  |   110 |
    17. |    3 | alice |   110 |
    18. +------+-------+-------+
    19. 2 rows in set (0.00 sec)
    20.  
    21. mysql> desc bs;
    22. +-------+-------------+------+-----+---------+-------+
    23. | Field | Type        | Null | Key | Default | Extra |
    24. +-------+-------------+------+-----+---------+-------+
    25. | id    | int(11)     | YES  |     | NULL    |       |
    26. | name  | varchar(10) | YES  |     | NULL    |       |
    27. | phone | int(11)     | YES  |     | NULL    |       |
    28. +-------+-------------+------+-----+---------+-------+
    29. 3 rows in set (0.00 sec)

    语法：create table 新表名（select  *   from  复制的表名   where  n=m）

    如果n等于m成立，那么复制表的结构与数据

    如果n等于m不成立，那么只复制表的结构

    MySQL的约束条件

    约束条件说明
    null 标识是否允许为空，默认为NULL。
    not null  标识该字段不能为空，可以修改。
    UNIQUE KEY  (UK)标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY
    default 为该字段设置默认值
    UNSIGNED    无符号，正数
    primary key   （PK） 标识该字段为该表的主键，可以唯一的标识记录，不可以为空
    auto_increment  标识该字段的值自动增长（整数类型，而且为主键）
    FOREIGN KEY (FK)    标识该字段为该表的外键，实现表与表（父表主键/子表1外键/子表2外键）之间的关联
    enum二选一
    set多选

    实验

    1. mysql> use zyq;   #进入zyq库
    2.  
    3. mysql>   create table idol(id int primary key auto_increment not null,
    4.          name varchar(40) not null comment 'idol姓名',
    5.          gender enum('男','女') not null default '男',
    6.          hobby set('唱歌','跳舞','打麻将') not null);
    7. Query OK, 0 rows affected (0.01 sec)
    8.  
    9.  
    10. mysql> desc idol;
    11. +--------+------------------------------------+------+-----+---------+----------------+
    12. | Field  | Type                               | Null | Key | Default | Extra          |
    13. +--------+------------------------------------+------+-----+---------+----------------+
    14. | id     | int(11)                            | NO   | PRI | NULL    | auto_increment |
    15. | name   | varchar(40)                        | NO   |     | NULL    |                |
    16. | gender | enum('男','女')                      NO   |     | 男      |                |
    17. | hobby  | set('唱歌','跳舞','打麻将')          | NO   |     | NULL    |                |
    18. +--------+------------------------------------+------+-----+---------+----------------+
    19. 4 rows in set (0.00 sec)

    primary key 是标识该字段为该表的主键，可以唯一的标识记录，不可以为空

    auto_increment  标识该字段的值自动增长（整数类型，而且为主键），每张表只能一个字段为自增

    not null  字段不可以为空，可以修改

    comment 指定注释

    default 默认

    set为多选

    enum为单选

    对于set多选，最后添加数据时候， insert into 表名（字段） values（'  ,  '）在一个引号里写即可，中间用逗号隔开。

    为表idol添加数据

    1. mysql> insert into idol(id,name,gender,hobby) values(1,'tom','男','唱歌');
    2. Query OK, 1 row affected (0.00 sec)
    3.  
    4. mysql> select * from idol;
    5. +----+------+--------+--------+
    6. | id | name | gender | hobby  |
    7. +----+------+--------+--------+
    8. |  1 | tom  | 男     | 唱歌   |
    9. +----+------+--------+--------+
    10.  
    11. mysql> insert into idol(id,name,gender,hobby) values(2,'jack','女','唱歌,跳舞');
    12. Query OK, 1 row affected (0.00 sec)
    13.  
    14. mysql> select * from idol;
    15. +----+------+--------+---------------+
    16. | id | name | gender | hobby         |
    17. +----+------+--------+---------------+
    18. |  1 | tom  | 男     | 唱歌          |
    19. |  2 | jack | 女     | 唱歌,跳舞     |
    20. +----+------+--------+---------------+
    21. 2 rows in set (0.00 sec)

    我们之前创建表的时候 设置约束条件 id可以为自增，性别gender默认为男，那么代表我们添加数据的时候 不定义字段id，gender 那么出来的表数据也会有id，gender

    1. mysql> insert into idol(name,hobby) values('cxk','唱歌');
    2. Query OK, 1 row affected (0.00 sec)
    3.  
    4. mysql> select * from idol;
    5. +----+------+--------+---------------+
    6. | id | name | gender | hobby         |
    7. +----+------+--------+---------------+
    8. |  1 | tom  | 男     | 唱歌          |
    9. |  2 | jack | 女     | 唱歌,跳舞     |
    10. |  3 | cxk  | 男     | 唱歌          |
    11. +----+------+--------+---------------+
    12. 3 rows in set (0.00 sec)
    13.

    MySQL之单表查询

    准备测试company 测试表employee5

    1. mysql> CREATE TABLE company.employee5(
    2.      id int primary key AUTO_INCREMENT not null,
    3.     name varchar(30) not null,
    4.     sex enum('male','female') default 'male' not null,
    5.      hire_date date not null,
    6.      post varchar(50) not null,
    7.      job_description varchar(100),
    8.      salary double(15,2) not null,
    9.      office int,
    10.      dep_id int
    11.      );
    12.  
    13. mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values 
    14. 	('jack','male','20180202','instructor','teach',5000,501,100),
    15. 	('tom','male','20180203','instructor','teach',5500,501,100),
    16. 	('robin','male','20180202','instructor','teach',8000,501,100),
    17. 	('alice','female','20180202','instructor','teach',7200,501,100),
    18. 	('tianyun','male','20180202','hr','hrcc',600,502,101),
    19. 	('harry','male','20180202','hr',NULL,6000,502,101),
    20. 	('emma','female','20180206','sale','salecc',20000,503,102),
    21. 	('christine','female','20180205','sale','salecc',2200,503,102),
    22.   ('zhuzhu','male','20180205','sale',NULL,2200,503,102),
    23.   ('gougou','male','20180205','sale','',2200,503,102);

    结果

    1. mysql> select * from employee5;
    2. +----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
    3. | id | name      | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
    4. +----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
    5. |  1 | jack      | male   | 2018-02-02 | instructor | teach           |  5000.00 |    501 |    100 |
    6. |  2 | tom       | male   | 2018-02-03 | instructor | teach           |  5500.00 |    501 |    100 |
    7. |  3 | robin     | male   | 2018-02-02 | instructor | teach           |  8000.00 |    501 |    100 |
    8. |  4 | alice     | female | 2019-03-05 | instructor | teach           |  7200.00 |    501 |    100 |
    9. |  5 | tianyun   | male   | 2018-02-02 | hr         | hrcc            |   600.00 |    502 |    101 |
    10. |  6 | harry     | male   | 2018-02-02 | hr         | NULL            |  6000.00 |    502 |    101 |
    11. |  7 | emma      | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
    12. |  8 | christine | female | 2018-02-05 | sale       | salecc          |  2200.00 |    503 |    102 |
    13. |  9 | zhuzhu    | male   | 2018-02-05 | sale       | NULL            |  2200.00 |    503 |    102 |
    14. | 10 | gougou    | male   | 2017-02-02 | sale       |                 |  2200.00 |    503 |    102 |
    15. +----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
    16. 10 rows in set (0.00 sec)

    查询：

    简单查询

    1. 查询表中一共有多少条数据
    2. mysql> select count(*) from employee5;
    3. +----------+
    4. | count(*) |
    5. +----------+
    6. |       10 |
    7. +----------+
    8. 1 row in set (0.00 sec)

    条件查询

    1. mysql> select * from employee5;
    2. mysql> select id ,name,sex from employee5;

    避免重复，去重查询： distinct

    语法： select distinct 字段 from 表名

    1. 查询去重之后的性别
    2. mysql> select distinct sex from employee5;
    3. +--------+
    4. | sex    |
    5. +--------+
    6. | male   |
    7. | female |
    8. +--------+
    9. 2 rows in set (0.00 sec)
    10.  
    11. 这是不去重的查询性别
    12.  
    13. mysql> select sex from  employee5;
    14. +--------+
    15. | sex    |
    16. +--------+
    17. | male   |
    18. | male   |
    19. | male   |
    20. | female |
    21. | male   |
    22. | male   |
    23. | female |
    24. | female |
    25. | male   |
    26. | male   |
    27. +--------+
    28. 10 rows in set (0.00 sec)

    通过计算查询

    1. 查询该表中每个人的工资
    2. mysql> select name,salary from employee5;
    3. +-----------+----------+
    4. | name      | salary   |
    5. +-----------+----------+
    6. | jack      |  5000.00 |
    7. | tom       |  5500.00 |
    8. | robin     |  8000.00 |
    9. | alice     |  7200.00 |
    10. | tianyun   |   600.00 |
    11. | harry     |  6000.00 |
    12. | emma      | 20000.00 |
    13. | christine |  2200.00 |
    14. | zhuzhu    |  2200.00 |
    15. | gougou    |  2200.00 |
    16. +-----------+----------+
    17. 10 rows in set (0.00 sec)
    18.  
    19. 假设表中工资是月薪，一年假设14薪。查询该表中每个人一年的工资
    20. mysql> select name,salary,salary*14 from employee5;
    21. +-----------+----------+-----------+
    22. | name      | salary   | salary*14 |
    23. +-----------+----------+-----------+
    24. | jack      |  5000.00 |  70000.00 |
    25. | tom       |  5500.00 |  77000.00 |
    26. | robin     |  8000.00 | 112000.00 |
    27. | alice     |  7200.00 | 100800.00 |
    28. | tianyun   |   600.00 |   8400.00 |
    29. | harry     |  6000.00 |  84000.00 |
    30. | emma      | 20000.00 | 280000.00 |
    31. | christine |  2200.00 |  30800.00 |
    32. | zhuzhu    |  2200.00 |  30800.00 |
    33. | gougou    |  2200.00 |  30800.00 |
    34. +-----------+----------+-----------+
    35. 10 rows in set (0.00 sec)

    支持四则运算查询

    支持起别名 语法： select  字段名，字段名  as 新字段名 from 表名

    1. 也可以起别名，比如查询一年的工资，起名为yearsalary 呈现出来
    2. mysql> select name,salary*14 as yearsalary from employee5;
    3. +-----------+------------+
    4. | name      | yearsalary |
    5. +-----------+------------+
    6. | jack      |   70000.00 |
    7. | tom       |   77000.00 |
    8. | robin     |  112000.00 |
    9. | alice     |  100800.00 |
    10. | tianyun   |    8400.00 |
    11. | harry     |   84000.00 |
    12. | emma      |  280000.00 |
    13. | christine |   30800.00 |
    14. | zhuzhu    |   30800.00 |
    15. | gougou    |   30800.00 |
    16. +-----------+------------+
    17. 10 rows in set (0.00 sec)

    显示格式自定义

     CONCAT() 函数用于连接字符串

    将几个数据拼接在一起，显示格式，可以自定义，

    1. 想查询每个人及对应的工资  拼接起来
    2. mysql> select concat(name,'@',salary) from employee5;
    3. +-------------------------+
    4. | concat(name,'@',salary) |
    5. +-------------------------+
    6. | jack@5000.00            |
    7. | tom@5500.00             |
    8. | robin@8000.00           |
    9. | alice@7200.00           |
    10. | tianyun@600.00          |
    11. | harry@6000.00           |
    12. | emma@20000.00           |
    13. | christine@2200.00       |
    14. | zhuzhu@2200.00          |
    15. | gougou@2200.00          |
    16. +-------------------------+
    17. 10 rows in set (0.01 sec)
    18.  
    19.  
    20. mysql> select concat(name,'#',salary) from employee5;
    21. +-------------------------+
    22. | concat(name,'#',salary) |
    23. +-------------------------+
    24. | jack#5000.00            |
    25. | tom#5500.00             |
    26. | robin#8000.00           |
    27. | alice#7200.00           |
    28. | tianyun#600.00          |
    29. | harry#6000.00           |
    30. | emma#20000.00           |
    31. | christine#2200.00       |
    32. | zhuzhu#2200.00          |
    33. | gougou#2200.00          |
    34. +-------------------------+
    35. 10 rows in set (0.00 sec)

    单条件查询

    1. 查询工资为5000的人
    2. mysql> select name from employee5 where salary=5000;
    3. +------+
    4. | name |
    5. +------+
    6. | jack |
    7. +------+
    8. 1 row in set (0.00 sec)
    9.  
    10. 查询性别为女的人
    11. mysql> select  name from employee5 where sex='male';
    12. +---------+
    13. | name    |
    14. +---------+
    15. | jack    |
    16. | tom     |
    17. | robin   |
    18. | tianyun |
    19. | harry   |
    20. | zhuzhu  |
    21. | gougou  |
    22. +---------+
    23. 7 rows in set (0.00 sec)

    多条件查询

    1. 查询工资在3000到5000之间的员工
    2. mysql> select name,salary from employee5 where salary >=3000 and salary <= 5000;
    3. +------+---------+
    4. | name | salary  |
    5. +------+---------+
    6. | jack | 5000.00 |
    7. +------+---------+
    8. 1 row in set (0.00 sec)
    也可以使用关键词  between  and
    1. 查询工资在2000到6000之间的员工
    2. mysql> select name,salary from employee5 where salary between 2000 and 3000;
    3. +-----------+---------+
    4. | name      | salary  |
    5. +-----------+---------+
    6. | christine | 2200.00 |
    7. | zhuzhu    | 2200.00 |
    8. | gougou    | 2200.00 |
    9. +-----------+---------+
    10. 3 rows in set (0.00 sec)
    关键字IS NULL  
    1. 查询职位描述为null的员工
    2. mysql> select name , job_description  from employee5 where job_description  is null;
    3. +--------+-----------------+
    4. | name   | job_description |
    5. +--------+-----------------+
    6. | harry  | NULL            |
    7. | zhuzhu | NULL            |
    8. +--------+-----------------+
    9. 2 rows in set (0.01 sec)
    1. 查询职位描述为 not null的员工
    2. mysql> select name , job_description  from employee5 where job_description  is not  null;
    3. +-----------+-----------------+
    4. | name      | job_description |
    5. +-----------+-----------------+
    6. | jack      | teach           |
    7. | tom       | teach           |
    8. | robin     | teach           |
    9. | alice     | teach           |
    10. | tianyun   | hrcc            |
    11. | emma      | salecc          |
    12. | christine | salecc          |
    13. | gougou    |                 |
    14. +-----------+-----------------+
    15. 8 rows in set (0.00 sec)
    1. 查询职位描述为'' 的员工
    2. mysql> select name , job_description  from employee5 where job_description = '';
    3. +--------+-----------------+
    4. | name   | job_description |
    5. +--------+-----------------+
    6. | gougou |                 |
    7. +--------+-----------------+
    8. 1 row in set (0.00 sec)

    NULL说明：
            1、等价于没有任何值、是未知数。
            2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
            3、对空值做加、减、乘、除等运算操作，结果仍为空。
            4、比较时使用关键字用“is null”和“is not null”。
            5、排序时比其他数据都小，所以NULL值总是排在最前。

    关键词in集合查询
    1. 查询工资是2000或者3000或者5000或者5500的员工
    2. mysql> select name , salary  from employee5 where salary =2000 or salary =3000 or salary =5000 or salary =5500;
    3. +------+---------+
    4. | name | salary  |
    5. +------+---------+
    6. | jack | 5000.00 |
    7. | tom  | 5500.00 |
    8. +------+---------+
    9. 2 rows in set (0.00 sec)
    10.  
    11. 也可以将他们放在一个集合里面查询
    12. mysql> select name , salary from employee5  where salary in(2000,5000,5500);
    13. +------+---------+
    14. | name | salary  |
    15. +------+---------+
    16. | jack | 5000.00 |
    17. | tom  | 5500.00 |
    18. +------+---------+
    19. 2 rows in set (0.00 sec)
    1. 查询工资不是5500，5000的员工
    2. mysql> select name , salary  from employee5 where salary  not in (5000,5500);
    3. +-----------+----------+
    4. | name      | salary   |
    5. +-----------+----------+
    6. | robin     |  8000.00 |
    7. | alice     |  7200.00 |
    8. | tianyun   |   600.00 |
    9. | harry     |  6000.00 |
    10. | emma      | 20000.00 |
    11. | christine |  2200.00 |
    12. | zhuzhu    |  2200.00 |
    13. | gougou    |  2200.00 |
    14. +-----------+----------+
    15. 8 rows in set (0.00 sec)

    排序查询

    1. 升序查询
    2. 升序查询名字首字母排列
    3. mysql> select name from employee5 order by name;
    4. +-----------+
    5. | name      |
    6. +-----------+
    7. | alice     |
    8. | christine |
    9. | emma      |
    10. | gougou    |
    11. | harry     |
    12. | jack      |
    13. | robin     |
    14. | tianyun   |
    15. | tom       |
    16. | zhuzhu    |
    17. +-----------+
    18. 10 rows in set (0.00 sec)
    1. 降序查询
    2. mysql> select name from employee5 order by name desc;
    3. +-----------+
    4. | name      |
    5. +-----------+
    6. | zhuzhu    |
    7. | tom       |
    8. | tianyun   |
    9. | robin     |
    10. | jack      |
    11. | harry     |
    12. | gougou    |
    13. | emma      |
    14. | christine |
    15. | alice     |
    16. +-----------+
    17. 10 rows in set (0.00 sec)

    语法： select  字段 from  表名 order by  字段      默认升序查询

      select  字段 from 表名 order  by  字段  desc      倒叙查询

    模糊查询：

    1. 查询工资中有数字20的员工
    2. mysql> select * from employee5 where salary like '%20%';
    3. +----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
    4. | id | name      | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
    5. +----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
    6. |  4 | alice     | female | 2019-03-05 | instructor | teach           |  7200.00 |    501 |    100 |
    7. |  7 | emma      | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
    8. |  8 | christine | female | 2018-02-05 | sale       | salecc          |  2200.00 |    503 |    102 |
    9. |  9 | zhuzhu    | male   | 2018-02-05 | sale       | NULL            |  2200.00 |    503 |    102 |
    10. | 10 | gougou    | male   | 2017-02-02 | sale       |                 |  2200.00 |    503 |    102 |
    11. +----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
    12. 5 rows in set (0.00 sec)

    正则查询

    1.查询有特定字符串或者字符的记录
    1. 查询 name 字段含有“a”的记录，SQL 语句和执行过程如下。
    2. mysql> select * from employee5 where name regexp 'a';
    3. +----+---------+--------+------------+------------+-----------------+----------+--------+--------+
    4. | id | name    | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
    5. +----+---------+--------+------------+------------+-----------------+----------+--------+--------+
    6. |  1 | jack    | male   | 2018-02-02 | instructor | teach           |  5000.00 |    501 |    100 |
    7. |  4 | alice   | female | 2019-03-05 | instructor | teach           |  7200.00 |    501 |    100 |
    8. |  5 | tianyun | male   | 2018-02-02 | hr         | hrcc            |   600.00 |    502 |    101 |
    9. |  6 | harry   | male   | 2018-02-02 | hr         | NULL            |  6000.00 |    502 |    101 |
    10. |  7 | emma    | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
    11. +----+---------+--------+------------+------------+-----------------+----------+--------+--------+
    12. 5 rows in set (0.00 sec)
    2.查询以特定字符或字符串开头的记录
    1. mysql> select * from employee5 where name regexp '^a';
    2. +----+-------+--------+------------+------------+-----------------+---------+--------+--------+
    3. | id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
    4. +----+-------+--------+------------+------------+-----------------+---------+--------+--------+
    5. |  4 | alice | female | 2019-03-05 | instructor | teach           | 7200.00 |    501 |    100 |
    6. +----+-------+--------+------------+------------+-----------------+---------+--------+--------+
    7. 1 row in set (0.00 sec)
    3.查询以特定字符或字符串结尾的记录
    1. mysql> select * from employee5 where name regexp 'y$';
    2. +----+-------+------+------------+------+-----------------+---------+--------+--------+
    3. | id | name  | sex  | hire_date  | post | job_description | salary  | office | dep_id |
    4. +----+-------+------+------------+------+-----------------+---------+--------+--------+
    5. |  6 | harry | male | 2018-02-02 | hr   | NULL            | 6000.00 |    502 |    101 |
    6. +----+-------+------+------------+------+-----------------+---------+--------+--------+
    7. 1 row in set (0.00 sec)
    4.使用{n,}或者{n,m}来指定字符串连续出现的次数

    字符串{n,}表示字符串连续出现 n 次；字符串{n,m}表示字符串连续出现至少 n 次，最多 m 次。

    例如，a{2,} 表示字母 a 连续出现至少 2 次，也可以大于 2 次；a{2,4} 表示字母 a 连续出现最少 2 次，最多不能超过 4 次。

    1. mysql> select * from employee5 where name regexp 'r{1}';
    2. +----+-----------+--------+------------+------------+-----------------+---------+--------+--------+
    3. | id | name      | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
    4. +----+-----------+--------+------------+------------+-----------------+---------+--------+--------+
    5. |  3 | robin     | male   | 2018-02-02 | instructor | teach           | 8000.00 |    501 |    100 |
    6. |  6 | harry     | male   | 2018-02-02 | hr         | NULL            | 6000.00 |    502 |    101 |
    7. |  8 | christine | female | 2018-02-05 | sale       | salecc          | 2200.00 |    503 |    102 |
    8. +----+-----------+--------+------------+------------+-----------------+---------+--------+--------+
    9. 3 rows in set (0.00 sec)
    5.‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a
    1. mysql> select * from employee5 where salary regexp '72+';
    2. +----+-------+--------+------------+------------+-----------------+---------+--------+--------+
    3. | id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
    4. +----+-------+--------+------------+------------+-----------------+---------+--------+--------+
    5. |  4 | alice | female | 2019-03-05 | instructor | teach           | 7200.00 |    501 |    100 |
    6. +----+-------+--------+------------+------------+-----------------+---------+--------+--------+
    7. 1 row in set (0.00 sec)

    函数

    1. 函数  
    2. count()  统计数量  
    3. max()     
    4. min()    
    5. avg()    
    6. database()  
    7. user()    
    8. now()    
    9. sum()    
    10. password()
    11. ​​   
    12.  SELECT COUNT(*) FROM employee5;   
    13.  SELECT COUNT(*) FROM employee5 WHERE dep_id=101;  
    14.  SELECT MAX(salary) FROM employee5;    
    15.  SELECT MIN(salary) FROM employee5;   
    16.  SELECT AVG(salary) FROM employee5;  
    17.  SELECT SUM(salary) FROM employee5;  
    18.  SELECT SUM(salary) FROM employee5 WHERE dep_id=101;     

    MySQL之安全控制

    1.  
    2.  
    3. 数据库不要暴露在公网；授权普通用户，注意远程连接地址。ip肯定不能弄成%。权限
    4.  
    5. 修改数据库端口。3306
    6.  
    7. 进程，使用mysql普通用户启动。
    8.  
    9. 启动binlog日志
    10.  
    11. 删除空口令账号，没有密码就可以登录
    12.  
    13. test（5.6）数据库删除
    14.  
    15. 禁止root用户远程访问
    16.  
    17. 记录慢日志
    18.  
    19. 调优： 配置最大连接数    max_connections=300

    1.确保MySQL运行用户为一般用户

    1. # groupadd mysql
    2. # useradd -M -s /sbin/nologin -g mysql mysql
    3.  
    4. # vim /etc/my.cnf
    5. user = mysql
    6.  
    7. #注意点:
    8. 	改变拥有者和所属组对于mysql的安装目录

    2.建议修改默认端口3306,改为其他的一些端口

    1. # vim /etc/my.cnf
    2. port = 3306 false
    3. port = 10086 true

    3.开启mysql二进制日志,在误删除数据的情况下,可以通过二进制日志恢复到某个时间点 ；；； 为了数据安全 ，为了好恢复

    1. # vim /etc/my.cnf
    2. log_bin = othername

    4..删除空口令账号

    1. #禁用匿名账号
    2. # vim /etc/my.cnf
    3. 	skip-grant-tables = 1. --改成 "#skip-grant-tables = 1"
    4.  
    5. #删除空口令用户
    6. mysql> SELECT User,Host,authentication_string from mysql.user;
    7. +---------------+-----------+-------------------------------------------+
    8. | User          | Host      | authentication_string                     |
    9. +---------------+-----------+-------------------------------------------+
    10. | root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
    11. | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    12. | mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    13. +---------------+-----------+-------------------------------------------+
    14. 3 rows in set (0.00 sec)
    15.  
    16. mysql> create user 'newrain'@'localhost';	#(这是在做实验)创建空口令账户
    17. Query OK, 0 rows affected (0.00 sec)
    18.  
    19. mysql> SELECT User,Host,authentication_string from mysql.user;
    20. +---------------+-----------+-------------------------------------------+
    21. | User          | Host      | authentication_string                     |
    22. +---------------+-----------+-------------------------------------------+
    23. | root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
    24. | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    25. | mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    26. | newrain      | localhost |                                           |
    27. +---------------+-----------+-------------------------------------------+
    28. 4 rows in set (0.00 sec)
    29.  
    30. mysql> drop user 'newrain'@'localhost';	#这是删除空口令账户
    31. Query OK, 0 rows affected (0.01 sec)
    32.  
    33. mysql> SELECT User,Host,authentication_string from mysql.user;
    34. +---------------+-----------+-------------------------------------------+
    35. | User          | Host      | authentication_string                     |
    36. +---------------+-----------+-------------------------------------------+
    37. | root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
    38. | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    39. | mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    40. +---------------+-----------+-------------------------------------------+
    41. 3 rows in set (0.00 sec)
    42.

    a.禁止root账户远程访问(允许普通用户远程访问，某个网段即可)

    1. mysql> SELECT User,Host,authentication_string from mysql.user;
    2. +---------------+-----------+-------------------------------------------+
    3. | User          | Host      | authentication_string                     |
    4. +---------------+-----------+-------------------------------------------+
    5. | root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
    6. | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    7. | mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    8. +---------------+-----------+-------------------------------------------+
    9. 3 rows in set (0.00 sec)
    10.  
    11. mysql> create user 'root'@'10.0.11.%' identified by "123";
    12. Query OK, 0 rows affected (0.00 sec)
    13.  
    14. mysql> SELECT User,Host,authentication_string from mysql.user;
    15. +---------------+-----------+-------------------------------------------+
    16. | User          | Host      | authentication_string                     |
    17. +---------------+-----------+-------------------------------------------+
    18. | root          | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
    19. | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    20. | mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    21. | root          | 10.0.11.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
    22. +---------------+-----------+-------------------------------------------+
    23. 4 rows in set (0.00 sec)
    24.  
    25. mysql> drop user 'root'@'10.0.11.%';
    26. Query OK, 0 rows affected (0.00 sec)

    b.使用mysql的时候,经常会遇到MySQL: ERROR 1040: Too many connections这样的问题,一种是访问量确实很高, MySQL服务器抗不住,这个时候就要考虑增加从服务器分散读压力,另外一种情况是MySQL配置文件中max_connections值过小, 这时就需要调整当前最大连接数

    1. ##设置最大连接数02
    2. 修改mysql配置文件my.cnf,在[mysqld]段中添加或修改max_connections值:
    3. max_connections=256

  • 相关阅读:
    Android JetPack~ DataBinding(数据绑定)(一) 集成与使用
    Vue 自定义组件中 v-model 的使用
    OpenCV笔记整理【傅里叶变换】
    埃及分数 ← IDA*
    解的思路、身份认证方法、密码破解方法、字典
    LeetCode 2591. 将钱分给最多的儿童
    基于SpringBoot+MyBatis-Plus的图书管理系统
    vscode用密钥文件连接ssh：如果一直要输密码怎么办
    Vue UI 组件库
    【TensorFlow&PyTorch】创建张量学习笔记
  • 原文地址：https://blog.csdn.net/m0_59933574/article/details/133604649