数据库ddl及dml的基本操作-白红宇

数据库ddl及dml的基本操作

阅读量：6934 次

发布时间：2019-06-27

本文共 18711 字，大约阅读时间需要 62 分钟。

主键:

PRIMARY KEY 主键   UNIQUE KEY 唯一键

DDL：   CREATE DATABASE  创建数据库    CREATE TABLE  创建表    CREATE USER  创建用户

DROP DATABASE  删除数据库   DROP TABLE  删除表    DROP USER  删除用户

GRANT  授权   REVOKE  取消授权    DML：    INSERT  插入数据    DELETE  删除数据    UPDATE  更新数据    SELECT  查询数据

管理数据库：   CREATE DATABASE DB_NAME;    DROP DATABASE DB_NAME;

SHOW DATABASES;

管理表：

CREATE TABLE [DB_NAME.]TABLE_NAME (COLUMN DEFINATION)   COLUMN DEFINATION:    (col1_name data_type [修饰符], col2_name data_type [修饰符])

查看表定义：   DESC TB_NAME

DROP TABLE TB_NAME;

管理用户：   CREATE USER USERNAME@HOST [IDENTIFIED BY 
'password'
];

HOST表示格式：

ip:  网络地址：

MySQL的字符通配符：   %: 匹配任意长度的任意字符     _: 匹配任意单个字符

DROP USER USERNAME@HOST;

GRANT 权限列表 ON DB_NAME.TB_NAME TO USERNAME@HOST [IDENTIFIED BY 
'new_pass'
];

权限列表：   ALL PRIVILEGES，可简写为ALL    DB_NAME：

*：所有库

TB_NAME：   *: 所有表

刷新授权表，以使得权限立即生效：   mysql> FLUSH PRIVILEGES;

REVOKE 权限列表 ON DB_NAME.DB_TABLE FROM USERNAME@HOST;

DML:

插入数据：

INSERT INTO tb_name [(col1, col2,...)] VALUE|VALUES (val1, val2,...)[,(val1, val2,...)];

mysql> INSERT INTO students (Name,Age,Gender,Class) VALUES (
'jerry'
,43,
'm'
,
'class 2'
),(
'Ou Yangfeng'
,77,
'm'
,
'Hamopai'
);

查询数据：

SELECT 字段列表 FROM 表 WHERE 条件子句 ORDER BY 字段;

组合条件：   and    or    not

BETWEEN start_value AND end_value;

LIKE: 模糊匹配   Name LIKE O%;

RLIKE：模式匹配   Name RLIKE 
'^O.*$'

删除数据：   DELETE FROM tb_name WHERE 条件子句 [LIMIT n];

更新数据：

UPDATE tb_name SET col1=new_value1 WHERE 条件子句;

mysql> create database zone; 创建zone数据库

Query OK, 1 row affected (0.00 sec)

mysql> show databases; 查看数据库

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

| Database |

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

| information_schema |

| dingchao |

| momo |

| mysql |

| test |

| wpdb |

| zone |

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

mysql> drop database zone; 删除数据库

Query OK, 1 row affected (0.00 sec)

mysql> show databases; 查看数据库

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

| Database |

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

| information_schema |

| dingchao |

| momo |

| mysql |

| test |

| wpdb |

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

6 rows in set (0.00 sec)

mysql> use dingchao; 使用数据库

Database changed

mysql> create table boss ( ID int unsigned not null unique key auto_increment,

Name char(10) not null, Age tinyint, Gender enum('N','M') not null, Gongzi char(15));

Query OK, 0 rows affected (0.08 sec) 创建表

mysql> show tables; 查看表

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

| Tables_in_dingchao |

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

| Class |

| boss |

| student |

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

3 rows in set (0.01 sec)

mysql> desc boss; 查看表的结构

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

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

| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |

| Age | tinyint(4) | YES | | NULL | |

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

5 rows in set (0.00 sec)

mysql> insert into boss (Name,Age,Gender,GongZI) VALUE ('sloary','22','N','4400'),

('mara','35','M','3600'),('jifu','77','N','5800'); 插入数据

Query OK, 3 rows affected (0.00 sec)

mysql> select * from boss;

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

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

| 1 | sloary | 22 | N | 4400 |

| 2 | mara | 35 | M | 3600 |

| 3 | jifu | 77 | N | 5800 |

| 4 | lili | 22 | N | 3800 |

| 5 | boy | 35 | M | 2600 |

| 6 | tom | 77 | M | 3000 |

| 7 | mary | 18 | N | 6000 |

| 8 | king | 28 | M | 4600 |

| 9 | hellen | 30 | M | 3000 |

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

9 rows in set (0.00 sec)

mysql> select * from boss where Age > 22 ; 选择行查询年龄大于22岁的人

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

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

| 2 | mara | 35 | M | 3600 |

| 3 | jifu | 77 | N | 5800 |

| 5 | boy | 35 | M | 2600 |

| 6 | tom | 77 | M | 3000 |

| 8 | king | 28 | M | 4600 |

| 9 | hellen | 30 | M | 3000 |

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

6 rows in set (0.00 sec)

mysql> select Name,Age,GongZI from boss where Age > 30; 查询年龄大于30岁人的工资

投影列选择行，记住前面多个字段要加 “，”

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

| Name | Age | GongZI |

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

| mara | 35 | 3600 |

| jifu | 77 | 5800 |

| boy | 35 | 2600 |

| tom | 77 | 3000 |

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

4 rows in set (0.00 sec)

INSERT INTO box (Name,Age,Gender,GongZI) VALUE ('sloary','22','N','4400'),

('mara','35','M','3600'),('jifu','77','N','5800'); 插入数据

mysql> show databases;

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

| Database |

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

| information_schema |

| dingchao |

| momo |

| mysql |

| test |

| wpdb |

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

mysql> select Name,Age,GongZI from boss where Age > 30 order by Age,GongZI; 升序

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

| Name | Age | GongZI |

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

| boy | 35 | 2600 |

| mara | 35 | 3600 |

| tom | 77 | 3000 |

| jifu | 77 | 5800 |

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

4 rows in set (0.00 sec)

mysql> select Name,Age,GongZI from boss where Age > 30 order by Age,GongZI desc; 降序

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

| Name | Age | GongZI |

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

| mara | 35 | 3600 |

| boy | 35 | 2600 |

| jifu | 77 | 5800 |

| tom | 77 | 3000 |

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

4 rows in set (0.00 sec)

mysql> show tables; 查看表

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

| Tables_in_dingchao |

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

| Class |

| boess |

| boss |

| student |

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

4 rows in set (0.01 sec)

mysql> delete from boess; 这个命令慎用一删除整张表都被删除啦

Query OK, 0 rows affected (0.00 sec)

mysql> delete from boss where Age =22 ;

Query OK, 2 rows affected (0.00 sec) 一般跟where 条件使用

mysql> select * from boss;

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

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

| 2 | mara | 35 | M | 3600 |

| 3 | jini | 77 | N | 5800 |

| 5 | boy | 35 | M | 2600 |

| 6 | tom | 77 | M | 3000 |

| 7 | mary | 18 | N | 6000 |

| 8 | king | 28 | M | 4600 |

| 9 | hellen | 30 | M | 3000 |

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

7 rows in set (0.00 sec)

mysql> update boss set Name= 'jini' where GongZI = '3000'; 更新数据

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from boss;

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

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

| 2 | mara | 35 | M | 3600 |

| 3 | jini | 77 | N | 5800 |

| 5 | boy | 35 | M | 2600 |

| 6 | jini | 77 | M | 3000 |

| 7 | mary | 18 | N | 6000 |

| 8 | king | 28 | M | 4600 |

| 9 | jini | 30 | M | 3000 |

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

7 rows in set (0.00 sec)

mysql> select * from boss;

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

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

| 2 | mara | 35 | M | 3600 |

| 3 | jini | 77 | N | 5800 |

| 5 | boy | 35 | M | 2600 |

| 6 | jini | 77 | M | 3000 |

| 7 | mary | 18 | N | 6000 |

| 8 | king | 28 | M | 4600 |

| 9 | jini | 30 | M | 3000 |

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

7 rows in set (0.00 sec)

mysql> update boss set Name= 'jimi' where GongZI = '5800'; 更新数据

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from boss;

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

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

| 2 | mara | 35 | M | 3600 |

| 3 | jimi | 77 | N | 5800 |

| 5 | boy | 35 | M | 2600 |

| 6 | jini | 77 | M | 3000 |

| 7 | mary | 18 | N | 6000 |

| 8 | king | 28 | M | 4600 |

| 9 | jini | 30 | M | 3000 |

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

7 rows in set (0.00 sec)

mysql> select * from boss;

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

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

| 2 | mara | 35 | M | 3600 |

| 3 | jimi | 77 | N | 5800 |

| 5 | boy | 35 | M | 2600 |

| 6 | jini | 77 | M | 3000 |

| 7 | mary | 18 | N | 6000 |

| 8 | king | 28 | M | 4600 |

| 9 | jini | 30 | M | 3000 |

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

7 rows in set (0.00 sec)

mysql> select * from boss where GongZI between 2000 and 5000; 查询工资介于2000-5000的人

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

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

| 2 | mara | 35 | M | 3600 |

| 5 | boy | 35 | M | 2600 |

| 6 | jini | 77 | M | 3000 |

| 8 | king | 28 | M | 4600 |

| 9 | jini | 30 | M | 3000 |

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

5 rows in set (0.00 sec)

mysql> select * from boss where Name like '%j%'; like 比较消耗资源，尽量少使用

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

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

| 3 | jimi | 77 | N | 5800 |

| 6 | jini | 77 | M | 3000 |

| 9 | jini | 30 | M | 3000 |

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

3 rows in set (0.01 sec)

mysql> select * from boss where Name rlike '^j.*$'; 模糊查找基于正则

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

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

| 3 | jimi | 77 | N | 5800 |

| 6 | jini | 77 | M | 3000 |

| 9 | jini | 30 | M | 3000 |

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

3 rows in set (0.00 sec)

mysql> show engines; 查看存储引擎

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

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

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |

| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

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

5 rows in set (0.00 sec)

mysql> show table status\G 查看表的属性

*************************** 1. row ***************************

Name: Class

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 1

Avg_row_length: 32

Data_length: 32

Max_data_length: 281474976710655

Index_length: 2048

Data_free: 0

Auto_increment: 2

Create_time: 2014-08-11 04:07:12

Update_time: 2014-08-11 04:14:15

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

mysql> show character set; 查看字符集

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

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

| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

| dec8 | DEC West European | dec8_swedish_ci | 1 |

| cp850 | DOS West European | cp850_general_ci | 1 |

| hp8 | HP West European | hp8_english_ci | 1 |

mysql> show collation; 查看排序规则

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

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

| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |

| big5_bin | big5 | 84 | | Yes | 1 |

| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |

| dec8_bin | dec8 | 69 | | Yes | 1 |

mysql> show global variables like '%new%'; 查看变量

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

| Variable_name | Value |

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

| new | OFF |

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

1 row in set (0.00 sec)

mysql> show global status like 'create';

Empty set (0.00 sec)

创建用户和授权

MariaDB [(none)]> create user dingchao@'192.168.%.%' identified by '1234'；

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> create user tom@'192.168.%.%' identified by '1234';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database dingchao;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> grant all on dingchao.* to dingchao@'192.168.%.%' identified by '1234'；

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on dingchao.* to tom@'192.168.%.%' identified by '1234';

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;

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

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

| 1 | dingchao | 40 | m | 11wanglou1 |

| 2 | tom | 40 | m | 11wanglou2 |

| 3 | peter | 30 | F | wushu |

| 4 | mary | 40 | m | 11wanglou2 |

| 5 | kim | 22 | F | wushu |

| 6 | jeny | 33 | m | 11wanglou2 |

| 7 | lili | 26 | F | wushu |

| 8 | jbod | 28 | m | 11wanglou2 |

| 9 | maki | 55 | F | wushu |

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

select * from student where Age >30; 选择行

mysql> select * from student where Age >30;

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

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

| 1 | dingchao | 40 | m | 11wanglou1 |

| 2 | tom | 40 | m | 11wanglou2 |

| 4 | mary | 40 | m | 11wanglou2 |

| 6 | jeny | 33 | m | 11wanglou2 |

| 9 | maki | 55 | F | wushu |

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

select Name,Age from student where Age >30; 投影列选择行

mysql> select Name,Age from student where Age >30;

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

| Name | Age |

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

| dingchao | 40 |

| tom | 40 |

| mary | 40 |

| jeny | 33 |

| maki | 55 |

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

创建用户和授权

MariaDB [(none)]> create user dingchao@'192.168.%.%' identified by '1234'；

Query OK, 0 rows affected (0.01 sec)

创建tom这个用户@可以从192.168.0.0这个网段上以密码1234 登录到服务器

MariaDB [(none)]> create user tom@'192.168.%.%' identified by '1234';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges; 刷新密码使用户生效，不然重启服务器

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database dingchao;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> grant all on dingchao.* to dingchao@'192.168.%.%' identified by '1234'；

Query OK, 0 rows affected (0.00 sec)

授权tom这个用户可以在dingchao这个数据库上创建所有的表、删除所有的表及数据

可以从192.168.0.0 这个网段以1234密码登录

MariaDB [(none)]> grant all on dingchao.* to tom@'192.168.%.%' identified by '1234';

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges; 刷新使授权生效

Query OK, 0 rows affected (0.00 sec)

查询数据库中授权的用户情况

MariaDB [mysql]> select User,Password,Host from user;

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

| User | Password | Host |

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

| root | *A4B6157319038724E3560894F7F932C8886EBFCF | localhost |

| root | *A4B6157319038724E3560894F7F932C8886EBFCF | 127.0.0.1 |

| tom | *A4B6157319038724E3560894F7F932C8886EBFCF | 192.168.%.% |

| root | *A4B6157319038724E3560894F7F932C8886EBFCF | 192.168.%.% |

| zbxuser | *A4B6157319038724E3560894F7F932C8886EBFCF | 172.16.%.% |

| zbxuser | *A4B6157319038724E3560894F7F932C8886EBFCF | node1.org.com |

| zbxuser | *24E65C3D3577DA6C2A596788CEAA02923A74B75D | localhost |

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

update

ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

建议使用GRANT语句进行授权，语句如下：

grant all privileges on *.* to root@'%' identified by "root";

---------------------------------------------------

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

ON maildb.*

TO 'mail'@'localhost'

IDENTIFIED by 'mailPASSWORD ';

创建snort数据库，创建snort用户，将snort库所有表的所有权限赋予用户snort。

mysql> create database snort;

Query OK, 1 row affected (0.06 sec)

创建数据库

mysql> use mysql;

进入mysql库

mysql> insert into user (Host,User,Password) values ("localhost","snort",PASSWORD("112233445566"));

创建用户，设置初始密码

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

使改动生效；注：如果不执行该指令，则无法正常执行后续指令。

mysql> grant all on snort.* to 'snort'@'localhost' ;

Query OK, 0 rows affected (0.00 sec)

将snort库的所有权限赋予 snort用户

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

使改动生效

mysql> show grants for snort@localhost;

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

| Grants for snort@localhost |

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

| GRANT USAGE ON *.* TO 'snort'@'localhost' IDENTIFIED BY PASSWORD '1e6b29186dd45e97' |

| GRANT ALL PRIVILEGES ON `snort`.* TO 'snort'@'localhost' |

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

2 rows in set (0.00 sec)

MySQL 赋予用户权限命令的简单格式可概括为：grant 权限 on 数据库对象 to 用户

一、grant 普通数据用户，查询、插入、更新、删除数据库中所有表数据的权利。

grant select on testdb.* to common_user@'%'

grant insert on testdb.* to common_user@'%'

grant update on testdb.* to common_user@'%'

grant delete on testdb.* to common_user@'%'

或者，用一条 MySQL 命令来替代：

grant select, insert, update, delete on testdb.* to common_user@'%'

二、grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限。

grant 创建、修改、删除 MySQL 数据表结构权限。

grant create on testdb.* to developer@'192.168.0.%' ;

grant alter on testdb.* to developer@'192.168.0.%' ;

grant drop on testdb.* to developer@'192.168.0.%' ;

grant 操作 MySQL 外键权限。

grant references on testdb.* to developer@'192.168.0.%' ;

grant 操作 MySQL 临时表权限。

grant create temporary tables on testdb.* to developer@'192.168.0.%' ;

grant 操作 MySQL 索引权限。

grant index on testdb.* to developer@'192.168.0.%' ;

grant 操作 MySQL 视图、查看视图源代码权限。

grant create view on testdb.* to developer@'192.168.0.%' ;

grant show view on testdb.* to developer@'192.168.0.%' ;

grant 操作 MySQL 存储过程、函数权限。

grant create routine on testdb.* to developer@'192.168.0.%' ; -- now, can show procedure status

grant alter routine on testdb.* to developer@'192.168.0.%' ; -- now, you can drop a procedure

grant execute on testdb.* to developer@'192.168.0.%' ;

三、grant 普通 DBA 管理某个 MySQL 数据库的权限。

grant all privileges on testdb to dba@'localhost'

其中，关键字 “privileges” 可以省略。

四、grant 高级 DBA 管理 MySQL 中所有数据库的权限。

grant all on *.* to dba@'localhost'

五、MySQL grant 权限，分别可以作用在多个层次上。

1. grant 作用在整个 MySQL 服务器上：

grant select on *.* to dba@localhost ; -- dba 可以查询 MySQL 中所有数据库中的表。

grant all on *.* to dba@localhost ; -- dba 可以管理 MySQL 中的所有数据库

2. grant 作用在单个数据库上：

grant select on testdb.* to dba@localhost ; -- dba 可以查询 testdb 中的表。

3. grant 作用在单个数据表上：

grant select, insert, update, delete on testdb.orders to dba@localhost ;

4. grant 作用在表中的列上：

grant select(id, se, rank) on testdb.apache_log to dba@localhost ;

5. grant 作用在存储过程、函数上：

grant execute on procedure testdb.pr_add to 'dba'@'localhost'

grant execute on function testdb.fn_add to 'dba'@'localhost'

六、查看 MySQL 用户权限

查看当前用户（自己）权限：

show grants;

查看其他 MySQL 用户权限：

show grants for dba@localhost;

七、撤销已经赋予给 MySQL 用户权限的权限。

revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：

grant all on *.* to dba@localhost;

revoke all on *.* from dba@localhost;

八、MySQL grant、revoke 用户权限注意事项

1. grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

2. 如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“

grant select on testdb.* to dba@localhost with grant option;

转载于:https://blog.51cto.com/cgc243652136qq/1744178

你可能感兴趣的文章

System.Security.Cryptography.CryptographicException，密钥集不存在

查看>>

敏捷团队中的QA由来

查看>>

gdb调试报错：Missing separate debuginfos, use: debuginfo-install glibc-XXX

查看>>

根据百度API获得经纬度，然后根据经纬度在获得城市信息

查看>>

mariadb 10.1查看per connection内存消耗

查看>>

重装MAC系统 “安装器有效负载签名检查失败” 解决方法

查看>>

(转) Supercharging Style Transfer

查看>>

JMeter性能测试，验证请求数据的准确性（wc命令）

查看>>

Python学习札记(二十三) 函数式编程4 sorted

查看>>

跟着百度学PHP[14]-PDO-优化驱动

查看>>

mysql的.sql文件头部 /*!32312 IF NOT EXISTS*/;

查看>>

ONVIF测试方法及工具

查看>>

《ArcGIS Runtime SDK for Android开发笔记》——数据制作篇：发布具有同步能力的FeatureService服务...

查看>>

Oracle快速克隆安装

查看>>

Spring Boot中使用JdbcTemplate访问数据库

查看>>

struts2的核心和工作原理

查看>>

一种快速统计SQL Server每个表行数的方法

查看>>

(zhuan) How to Train Neural Networks With Backpropagation