基础知识
主键: 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; 查看表的结构
+--------+------------------+------+-----+---------+----------------+
| 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 ; 选择行 查询年龄大于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; 查询年龄大于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;
+----+--------+------+--------+--------+
| 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; 查询工资介于2000-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 |
+----------+------+