首页 > 代码库 > 数据库ddl及dml的基本操作
数据库ddl及dml的基本操作
基础知识
主键:
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;
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;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | char(10) | NO | | NULL | |
| Age | tinyint(4) | YES | | NULL | |
| Gender | enum(‘N‘,‘M‘) | NO | | NULL | |
| GongZI | char(15) | 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;
+----+--------+------+--------+--------+
| ID | Name | Age | Gender | GongZI |
+----+--------+------+--------+--------+
| 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 ; 选择行
+----+--------+------+--------+--------+
| ID | Name | Age | Gender | GongZI |
+----+--------+------+--------+--------+
| 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;
投影列 选择行 ，记住前面多个字段要加 “，”
+------+------+--------+
| 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‘);
create table Class (id int unsigned not null unique key auto_increment,
Class varchar(20), chenji char(10), xueli varchar(20));
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;
+----+--------+------+--------+--------+
| ID | Name | Age | Gender | GongZI |
+----+--------+------+--------+--------+
| 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;
+----+------+------+--------+--------+
| ID | Name | Age | Gender | GongZI |
+----+------+------+--------+--------+
| 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;
+----+------+------+--------+--------+
| ID | Name | Age | Gender | GongZI |
+----+------+------+--------+--------+
| 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;
+----+------+------+--------+--------+
| ID | Name | Age | Gender | GongZI |
+----+------+------+--------+--------+
| 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;
+----+------+------+--------+--------+
| ID | Name | Age | Gender | GongZI |
+----+------+------+--------+--------+
| 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;
+----+------+------+--------+--------+
| ID | Name | Age | Gender | GongZI |
+----+------+------+--------+--------+
| 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 比较消耗资源，尽量少使用
+----+------+------+--------+--------+
| ID | Name | Age | Gender | GongZI |
+----+------+------+--------+--------+
| 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.*$‘; 模糊查找基于正则
+----+------+------+--------+--------+
| ID | Name | Age | Gender | GongZI |
+----+------+------+--------+--------+
| 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; 查看存储引擎
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| 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; 查看字符集
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| 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 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
mysql> show collation; 查看排序规则
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| 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;
+----+----------+------+--------+------------+
| ID | Name | Age | Gender | Class |
+----+----------+------+--------+------------+
| 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;
+----+----------+------+--------+------------+
| ID | Name | Age | Gender | Class |
+----+----------+------+--------+------------+
| 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 |
+----------+------+