MySQL 基础篇-1
2017-08-15 本文已影响0人
Aaron_Alphabet
DDL语句
创建数据库
CREATE DATABASE dbname;
查看数据库
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| database_backup |
| database_test |
| mysql |
| performance_schema |
| sys |
+--------------------+
删除数据库
DROP DATABASE dbname;
打开数据库
USE dbname;
查看数据库中的数据表
SHOW TABLES;
创建数据表
创建
mysql> CREATE TABLE emp(
-> ename varchar(10),
-> hiredata date,
-> sal decimal(10,2),
-> detno int(2));
查看表的定义
mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredata | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| detno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
查看表结构
mysql> SHOW COLUMNS FROM emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredata | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| detno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
查看完整表结构
mysql> SHOW CREATE TABLE emp;
+-------+----------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------+
| emp | CREATE TABLE `emp` (
`ename` varchar(10) DEFAULT NULL,
`hiredata` date DEFAULT NULL,
`sal` decimal(10,2) DEFAULT NULL,
`detno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------+
删除表
DROP TABLE tablename;
修改表
修改表类型
ALTER TABLE tablename MODIFY[COLUMN] column_definition[FIRST AFTER col_name]
mysql> desc emp ;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredata | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| detno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
mysql> ALTER TABLE emp MODIFY ename VARCHAR(20);
mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredata | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| detno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
增加字段
ALTER TABLE tablename ADD [COLUMN] column_definiton [FIRST|AFTER col_name]
mysql> ALTER TABLE emp ADD age INT(3);
mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredata | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| detno | int(2) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
删除字段
ALTER TABLE tablename DROP [COLUMN] col_name;
mysql> ALTER TABLE emp DROP age;
字段改名
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]
mysql> ALTER TABLE emp CHANGE age age_new int(5);
修改字段排列顺序
ALTER TABLE emp ADD birthdate DATE AFTER ename; ------------ 新增字段在指定位置
mysql> ALTER TABLE emp MODIFY age_new int(3) FIRST; ---------- 将age_new 放在最前面
更改表名
ALTER TABLE tablename RENAME [TO] new_tablename;
mysql> ALTER TABLE emp RENAME emp_new;
DML语句
增（insert）
删（delete）
改（update）
查（select）
插入记录
INSERT INTO tablename(field1,field2,...,fieldn) VALUES(value1,value2,...,valuen)
INSERT INTO emp(ename,hiredata,sal,detno) VALUES("lesa","2003-1-5","3000",2);
插入多条记录
INSERT INTO tablename(field1,field2,...,fieldn)
VALUES
(value1,value2,...,valuen),
(value1,value2,...,valuen),
(value1,value2,...,valuen);
更新记录
UPDATE tablename SET field1=value1, field2=value2,..., fieldn=valuen [WHERE CONDITION]
mysql> UPDATE emp SET hiredata="2001-1-1" WHERE ename="zzx1";
mysql> SELECT * FROM emp;
+-------+------------+---------+-------+
| ename | hiredata | sal | detno |
+-------+------------+---------+-------+
| zzx1 | 2001-01-01 | 2000.00 | 1 |
| lesa | 2003-01-05 | 3000.00 | 2 |
+-------+------------+---------+-------+
同时更新多个表的数据
UPDATE t1,t2,...,tn SET t1.field1=expr1,..., tn.filedn=exprn [WHERE CONDITION];
删除记录
DELETE FROM tablename [WHERE CONDITION]
DELETE t1,t2,...,tn FROM t1,t2,...,tn [WHERE CONDITION]
查询记录
SELECT * FROM tablename [WHERE CONDITION]
查询不重复的记录 ---关键词：DISTINCT
mysql> SELECT * FROM emp;
+-------+------------+---------+-------+
| ename | hiredata | sal | detno |
+-------+------------+---------+-------+
| zzx1 | 2001-01-01 | 2000.00 | 1 |
| lesa | 2003-01-05 | 3000.00 | 2 |
| akon | 2016-05-03 | 3000.00 | 1 |
+-------+------------+---------+-------+
mysql> SELECT DISTINCT detno FROM emp;
+-------+
| detno |
+-------+
| 1 |
| 2 |
+-------+
条件查询---- WHERE
SELECT * FROM emp WHERE deptno=1;
条件：
比较运算符：= > < <= >= !=
逻辑运算符：OR AND
排序和限制 ----- ORDER BY LINMIT
SELECT * FROM tablename
[WHERE CONDITION]
[ORDER BY
field1 [DESC/ASC],
field2 [DESC/ASC],
...,
fieldn [DESC/ASC]
];
升序或者降序
mysql> SELECT * FROM emp ORDER BY sal DESC;
+-------+------------+---------+-------+
| ename | hiredata | sal | detno |
+-------+------------+---------+-------+
| sam | 2015-02-06 | 4500.00 | 3 |
| lesa | 2003-01-05 | 3000.00 | 2 |
| akon | 2016-05-03 | 3000.00 | 1 |
| zzx1 | 2001-01-01 | 2000.00 | 1 |
+-------+------------+---------+-------+
mysql> SELECT * FROM emp ORDER BY sal ASC;
+-------+------------+---------+-------+
| ename | hiredata | sal | detno |
+-------+------------+---------+-------+
| zzx1 | 2001-01-01 | 2000.00 | 1 |
| lesa | 2003-01-05 | 3000.00 | 2 |
| akon | 2016-05-03 | 3000.00 | 1 |
| sam | 2015-02-06 | 4500.00 | 3 |
+-------+------------+---------+-------+
SELECT ... FROM tablename
[LIMIT offset_start,row_count]
聚合
SELECT [field1,field2,...,fieldn] fun_name
FROM tablename
[WHERE condition]
[GROUP BY field1,field2,...,fieldn
[WITH ROLLUP]]
[HAVING where_condition]
参数说明.png
套路
SELECT ... FROM ...
WHERE...
GUROP BY...
HAVING...
ORDER BY...
LIMIT...
表连接
内连接--INNER JOIN ... ON ...
mysql> SELECT ename,deptname FROM emp INNER JOIN dept ON emp.deptno=dept.deptno;
+-------+----------+
| ename | deptname |
+-------+----------+
| zzx1 | tech |
| lesa | sale |
| akon | tech |
| sam | hr |
+-------+----------+
mysql> SELECT ename,deptname FROM emp ,dept WHERE emp.deptno=dept.deptno;
+-------+----------+
| ename | deptname |
+-------+----------+
| zzx1 | tech |
| lesa | sale |
| akon | tech |
| sam | hr |
+-------+----------+
外连接(左连接、右连接)
定义说明.png
mysql> SELECT ename,deptname FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno;
+-------+----------+
| ename | deptname |
+-------+----------+
| zzx1 | tech |
| akon | tech |
| lesa | sale |
| sam | hr |
| tony | NULL |
+-------+----------+
mysql> SELECT ename,deptname FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno;
+-------+----------+
| ename | deptname |
+-------+----------+
| zzx1 | tech |
| lesa | sale |
| akon | tech |
| sam | hr |
+-------+----------+
子查询
用于子查询的关键字：in、not in、=、!= 、exists、not exists
mysql> SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept);
+-------+------------+---------+--------+
| ename | hiredata | sal | deptno |
+-------+------------+---------+--------+
| zzx1 | 2001-01-01 | 2000.00 | 1 |
| lesa | 2003-01-05 | 3000.00 | 2 |
| akon | 2016-05-03 | 3000.00 | 1 |
| sam | 2015-02-06 | 4500.00 | 3 |
+-------+------------+---------+--------+
联合查询
SELECT * FROM t1
UNION | UNION ALL
SELECT * FROM t2
......
UNION | UNION ALL
SELECT * FROM tN
UNION 是将 UNION ALL 后的结果进行一次 DISTINCT
DCL 语句
DBA用来管理西永的对象权限时使用
数据表示例：
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredata | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| detno | int(2) | YES | | NULL | |
| age_new | int(5) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
mysql> SELECT * FROM dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | tech |
| 2 | sale |
| 3 | hr |
+--------+----------+
