你的位置:首页 > 数据库

[数据库]mariadb DML语句及用户授权


DML(Data Manipulation Language):INSERT, DELETE, UPDATE, SELECT

    INSERT  [INTO]  tbl_name  [(col1,...)]  {VALUES|VALUE}  (val1, ...),(...),...

 

假如有上面这张表

插入一行数据:insert students values(1,'xijinping',51,'zhonglanhai','m');  字符串必须加引号,数字不能加引号

同时插入多行数据:insert students (id,name) values (2,'hujingtao'),(3,'jiangzeming');

 

SELECT:

查看一张表的全部字段:select * from students;

只查看指定的字段:select id,name from students;

将字段id取个别名:select id as stuid,name from students;

WHERE clause:用于指明挑选条件

    col_name 操作符 value, 例如:age > 30

    操作符(1) :>, <, >=, <=, ==, !=

    例如:select * from students where id=1;

select * from students where name='jiangzemin';    注意:后面的字符串要加引号,是否区分大小写,取决于字符类型,如果是

binary或者varbinary则区分大小写,char或者varchar则不需要区分大小写

 

组合条件:or,and,not

  select name,age from students where age>=30 and age<=50;

  select name,age from students where age between 30 and 50; 跟上面的语句是一个意思

操作符 :BETWEEN ...  AND ...    LIKE 'PATTERN'  RLIKE  'PATTERN'(正则表达式对字符串做模式匹配)   IS NULL  

IS NOT NULL

    select name from students where name like '%ji%';  注意像like,rlike这种查询方式性能极低

    %:任意长度的任意字符  _:任意单个字符

    select name from students where name rlike 'min$';

    select name,age from students where age is null; 

    select id,name from students order by name;  根据name字段中的第一个字母排序,默认升序,加desc改为降序

注意其实执行DML语句首先都执行了select查询

  

DELETE:删除是相对应于行的

     (1) DELETE  FROM  tbl_name  WHERE where_condition

     (2) DELETE  FROM  tbl_name  [ORDER BY ...]  [LIMIT row_count]

       delete from students; (删除表的所有行)

     delete from students where age is null;

小技巧:1. 快速创建相同结构的表,包括索引:MariaDB [test1]> create table a like students;

    2.快速创建一个相同结构的表,但是不创建索引:MariaDB [test1]> create table b select * from students limit 0;

    

[root@localhost ~]# for i in {1..100}; do AGE=$[$RANDOM%100]; mysql -e "insert test1.a (id,name,age) values

($i,\"stu$i\",$AGE);"; done    为a这张表加入一些数据

    delete from a order by age desc limit 20;

 

UPDATE:

    update a set age=age-5 order by id desc limit 10;

    update a set age=age-5 where name not like 'stu__';

 

用户账号及权限管理:

    用户账号:'username'@'host'

    禁止检查主机名:在my.cnf配置文件中的[mysqld]段添加skip_name_resolve = ON  一般都得添加此选项以提升性能

    创建用户账号:CREATE  USER   'username'@'host'  [IDENTIFIED BY  'password'];

    例如:create user 'testuser'@'192.168.%.%' identified by 'testpass'; 记得刷新用户授权表flush privileges;

    删除用户账号:DROP USER  'user'@'host' [, user@host] ...

    例如:drop user 'testuser'@'192.168.%.%';

查看用户授权表命令:select * from mysql.user\G;

    

授权:GRANT  priv_type,...  ON  [object_type]  db_name.tbl_name  TO  'user'@'host'  [IDENTIFIED BY  'password'];

  例如:grant select,insert on test1.students to 'testuser'@'192.168.%.%';

 

查看指定用户所获得的授权:SHOW GRANTS FOR  'user'@'host';    SHOW GRANTS FOR CURRENT_USER;

回收权限:REVOKE  priv_type, ...  ON  db_name.tbl_name  FROM  'user'@'host';

     例如:revoke insert on test1.students from 'testuser'@'192.168.%.%';

 

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

(1) GRANT或REVOKE命令等执行的权限操作会保存于表中,MariaDB此时一般会自动重读授权表,权限修改会立即生效

(2) 其它方式实现的权限修改,要想生效,必须手动运行FLUSH PRIVILEGES命令

 

mariadb安装完后密码是空的,这是不安全的,看下图;

 

可以使用该命令完成root密码设定:[root@localhost ~]# mysql_secure_installation