MySQL语法和用户授权

2023-08-27 16:25:40

管理数据库

create database 等同于 create schema

#导入数据库脚本
MariaDB [db1]> source /root/mysql/hellodb_innodb.sql

管理数据表

数据类型:
1.数字类型
2.时间类型
3.字符串类型

timestamp 只要表中任何一个字段发生修改,自动存储记录时间精确到秒

创建表:
MariaDB [db1]> create table myuser select user,host,password from mysql.user;

表结构一旦定义好,通常不建议进行修改

CASCADE 表示级联删除一般指的是主外键的关系

选择数据类型
set 多选
enum 单选

复合主键
primary key(name,city)

快速删除(不可恢复)
truncate table students;

生产环境一般用update标志位来代替删除操作

SQL基础语法

查询语句写法不同会造成巨大的性能差别,一条很差的查询可能导致服务器宕机

 6记录不是null 7记录才是null  null会在对应的位置显示NULL字符

 不能通过=null来查询, 只能是 is null 或者 is not null来过滤

MariaDB [db1]> select * from students;

+----+------+-------+------+-------+

| id | name | phone | sex  | score |

+----+------+-------+------+-------+

|  1 | aa   | 111   | f    |    80 |

|  2 | bbb  | 2222  | m    |    90 |

|  3 | cccc | 33333 | f    |    80 |

|  4 | ffff | 44444 | m    |    80 |

|  5 | ff   | 555   | f    |    90 |

|  6 | 666  |       | m    |    40 |

|  7 | 777  | NULL  | m    |    40 |

null处理机制

MariaDB [db1]> select sex from students group by sex;

#表示sex这个列有几个不同的值就会被分成几个组

#分完组之后就可以对数据进行相关汇总了

+------+

| sex  |

+------+

| f    |

| m    |

+------+

group by 语法注意点:

  1.select 后面只能加分组的列本身

  2.各种聚合函数

  分组后的过滤条件语法只能使用having 

多个分组

 MariaDB [db1]> select class,sex,avg(score) from students group by class,sex;

+-------+------+------------+

| class | sex  | avg(score) |

+-------+------+------------+

|     1 | f    | 80.0000    |

|     1 | m    | 85.0000    |

|     2 | f    | 60.0000    |

|     2 | m    | 40.0000    |

+-------+------+------------+

group by语法

 1.内连接

   取两张表的交集  也可以取多张表的交集

  select s.name,t.name from students s,teachers t where s.teacherid=t.tid;

  select s.name,t.name from students s inner join teachers t on s.teacherid=t.tid;

 2.交叉连接

    两张表的记录分别组合一遍

    select * from students cross join teachers;

 3.左外连接(谁左谁右十分重要)

    默认取左边表的全部记录,取右边表和左边表有交集的记录.

    可取左表和右表没有交集的记录 添加一个过滤条件即可

    and left.id is null

 4.右外连接(谁左谁右十分重要)

   取右边表的全部记录,取左边表和右边表有交集的记录.

   取右表记录和左表没有交集的记录

   and right.id is null

 5.自连接 把一张表当成两张表来使用

    select s1.name,s2.name from students as s1 inner join students as s2 on s1.id=s2.id

 6.union连接 竖直排列组合显示

   两个条件:

     1.两个select查询的列的数量必须一致

     2.两个select查询的对应的列的数据类型必须一致

   select id,name from students union select id,name from teachers

连接查询

 select * from students where stuid in(select stuid from scores where score > (select avg(score) from scores));

子查询

select查询语句执行流程

1. 先执行from 指定对哪张表进行操作
2. 执行where 过滤表中的行
3 . 执行group by 分组
4. 执行having 对分组后的结果过滤
5. 执行order by 排序
6. 执行select columns 挑选需要显示的字段
7. 执行limit 限定需要显示的记录条数

select查询出来的数据默认是按照磁盘存放次序来排序显示的

select 语法有点类似于awk

= 是精确匹配 where name='abc'

select 可以给字段和表取别名

like 模糊搜索查询
% 任意个任意字符
- 任意单个字符

 MariaDB [db1]> select 'hello word'

    -> ;

+------------+

| hello word |

+------------+

| hello word |

+------------+

1 row in set (0.00 sec)

MariaDB [db1]> select 1+2;

+-----+

| 1+2 |

+-----+

|   3 |

+-----+

MariaDB [db1]> select '1+2',1+2 from user1;

+-----+-----+

| 1+2 | 1+2 |

+-----+-----+

| 1+2 |   3 |

| 1+2 |   3 |

| 1+2 |   3 |

| 1+2 |   3 |

MariaDB [db1]> desc user1;

+----------+----------+------+-----+---------+-------+

| Field    | Type     | Null | Key | Default | Extra |

+----------+----------+------+-----+---------+-------+

| user     | char(80) | NO   |     |         |       |

| host     | char(60) | NO   |     |         |       |

| password | char(41) | NO   |     |         |       |

+----------+----------+------+-----+---------+-------+

MariaDB [db1]> select user as "姓名",host as "主机" from user1;

+--------+-----------+

| 姓名   | 主机      |

+--------+-----------+

| root   | localhost |

| root   | centos7   |

| root   | 127.0.0.1 |

| root   | ::1       |

+--------+-----------+

 MariaDB [db1]> select class,sex,avg(score) from students group by class,sex;

+-------+------+------------+

| class | sex  | avg(score) |

+-------+------+------------+

|     1 | f    | 80.0000    |

|     1 | m    | 85.0000    |

|     2 | f    | 60.0000    |

|     2 | m    | 40.0000    |

+-------+------+------------+

MariaDB [hellodb]> select s.name,sc.score,c.course from students s,scores sc,courses c where s.stuid=sc.stuid and sc.courseid=c.courseid limit 5;

+-------------+-------+----------------+

| name        | score | course         |

+-------------+-------+----------------+

| Shi Zhongyu |    77 | Kuihua Baodian |

| Shi Zhongyu |    93 | Weituo Zhang   |

| Shi Potian  |    47 | Kuihua Baodian |

| Shi Potian  |    97 | Daiyu Zanghua  |

| Xie Yanke   |    88 | Kuihua Baodian |

+-------------+-------+----------------+

MariaDB [hellodb]> select s.name,sc.score,c.course from (select * from students limit 5) s,scores sc,courses c where s.stuid=sc.stuid and sc.courseid=c.courseid;

+-------------+-------+----------------+

| name        | score | course         |

+-------------+-------+----------------+

| Shi Zhongyu |    77 | Kuihua Baodian |

| Shi Zhongyu |    93 | Weituo Zhang   |

| Shi Potian  |    47 | Kuihua Baodian |

| Shi Potian  |    97 | Daiyu Zanghua  |

| Xie Yanke   |    88 | Kuihua Baodian |

| Xie Yanke   |    75 | Weituo Zhang   |

| Ding Dian   |    71 | Daiyu Zanghua  |

| Ding Dian   |    89 | Kuihua Baodian |

| Yu Yutong   |    39 | Hamo Gong      |

| Yu Yutong   |    63 | Dagou Bangfa   |

+-------------+-------+----------------+

MariaDB [hellodb]> select re.name,avg(score) from (select s.name,sc.score,c.course from (select * from students limit 8) s,scores sc,courses c where s.stuid=sc.stuid and sc.courseid=c.courseid ) as re group by name order by avg(score) desc;

+-------------+------------+

| name        | avg(score) |

+-------------+------------+

| Shi Qing    | 96.0000    |

| Shi Zhongyu | 85.0000    |

| Xi Ren      | 84.5000    |

| Xie Yanke   | 81.5000    |

| Ding Dian   | 80.0000    |

| Lin Daiyu   | 75.0000    |

| Shi Potian  | 72.0000    |

| Yu Yutong   | 51.0000    |

+-------------+------------+

MariaDB [hellodb]> select c.course,re.stucount from courses as c,(select courseid,count(stuid) as stucount from scores group by courseid) as re where c.courseid=re.courseid;

+----------------+----------+

| course         | stucount |

+----------------+----------+

| Hamo Gong      |        3 |

| Kuihua Baodian |        4 |

| Jinshe Jianfa  |        1 |

| Taiji Quan     |        1 |

| Daiyu Zanghua  |        2 |

| Weituo Zhang   |        2 |

| Dagou Bangfa   |        2 |

+----------------+----------+

MariaDB [hellodb]> select * from students where age > (select avg(age) from students);

+-------+--------------+-----+--------+---------+-----------+

| StuID | Name         | Age | Gender | ClassID | TeacherID |

+-------+--------------+-----+--------+---------+-----------+

|     3 | Xie Yanke    |  53 | M      |       2 |        16 |

|     4 | Ding Dian    |  32 | M      |       4 |         4 |

|     6 | Shi Qing     |  46 | M      |       5 |      NULL |

|    13 | Tian Boguang |  33 | M      |       2 |      NULL |

|    25 | Sun Dasheng  | 100 | M      |    NULL |      NULL |

+-------+--------------+-----+--------+---------+-----------+

MariaDB [hellodb]> select name from students where stuid in(select stuid from scores where courseid in(1,2,4,7));

+-------------+

| name        |

+-------------+

| Shi Zhongyu |

| Shi Potian  |

| Xie Yanke   |

| Ding Dian   |

| Yu Yutong   |

| Shi Qing    |

| Xi Ren      |

| Lin Daiyu   |

+-------------+

MariaDB [hellodb]> select s.name,s.age from students s,(select classid,avg(age) as age,count(stuid)from students group by classid having count(stuid)>=3) as re where s.classid = re.classid and s.age > re.age;

+---------------+-----+

| name          | age |

+---------------+-----+

| Shi Potian    |  22 |

| Xie Yanke     |  53 |

| Ding Dian     |  32 |

| Yu Yutong     |  26 |

| Yuan Chengzhi |  23 |

| Xu Zhu        |  21 |

| Lin Chong     |  25 |

| Hua Rong      |  23 |

| Huang Yueying |  22 |

+---------------+-----+

9 rows in set (0.00 sec)

查询实例

视图

VIEW,虚表保存有实表的查询结果

视图中的数据事实上存储于“基表”中因此,其修改操作也会针对基表实现; 其修改操作受基表限制

函数

系统函数和自定义函数保存在mysql.proc表中

存储过程

存储过程存储过程保存在mysql.proc表中

触发器

trigger_name：触发器的名称

trigger_time：{ BEFORE | AFTER },表示在事件之前或之后触发

trigger_event:：{ INSERT |UPDATE | DELETE },触发的具体事件

tbl_name：该触发器作用在表名

触发器的执行不是由程序调用, 也不是由手工启动,而是由事件来触发,激活从而实现执行

触发器实例,在向学生表INSERT数据时,学生数增加,DELETE学生时,学生数减少

MySQL用户和授权管理

用户管理

创建用户：CREATE USER

CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']；默认权限：USAGE 只能连接不能进行任何操作

用户重命名：RENAME USER RENAME USER old_user_name TO new_user_name

删除用户： DROP USER 'USERNAME'@'HOST‘

示例：删除默认的空用户 DROP USER ''@'localhost';

修改密码：

mysql>SET PASSWORD FOR 'user'@'host' = PASSWORD(‘password');

mysql>UPDATE mysql.user SET password=PASSWORD('your_password') WHERE clause;

此方法需要执行下面指令才能生效：mysql> FLUSH PRIVILEGES;

#mysqladmin -u root –poldpass password ‘newpass‘

忘记管理员密码的解决办法：

启动mysqld进程时,为其使用如下选项: --skip-grant-tables --skip-networking 修改配置文件/etc/my.cnf中的mysqld

使用UPDATE命令修改管理员密码

关闭mysqld进程,移除上述两个选项,重启mysqld

权限管理

授权和创建用户可以都通过grant命令一步来实现,因为grant授权的用户如果不存在,mysql会自动创建对应的用户

权限类别：

1.数据库级别

2.表级别

3.字段级别

4.管理类

5.程序类

授权语法:

GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host' [IDENTIFIED BY 'password']

priv_type: ALL [PRIVILEGES]

object_type:TABLE | FUNCTION | PROCEDURE

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

MariaDB [(none)]> grant all on hellodb.* to yxh@"192.168.%.%" identified by 'root';
MariaDB [(none)]> grant select,insert on hellodb.* to laoge@'%' identified by 'root';

MariaDB [(none)]> grant select(stuid,name) on hellodb.students to dage@'%' identified by 'root';

MariaDB [(none)]> show grants for yxh@"192.168.%.%";

回收授权语法:

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...

示例: REVOKE DELETE ON testdb.* FROM 'testuser'@'%'

查看用户的授权信息:

Help SHOW GRANTS

SHOW GRANTS FOR 'user'@'host';

SHOW GRANTS FOR CURRENT_USER[()];

注意：MariaDB服务进程启动时会读取mysql库中所有授权表至内存

(1) GRANT或REVOKE等执行权限操作会保存于系统表中,MariaDB的服务进程通常会自动重读授权表,使之生效

(2) 对于不能够或不能及时重读授权表的命令,可手动让MariaDB的服务进程重读授权表

mysql> FLUSH PRIVILEGES;

码农公寓