一、简介
MySQL是由David Axmark、Allan Larsson和Michael Widenius3个瑞典人于20世纪90年代开发的一个关系型数据库管理系统,主要用于存储和管理数据。所谓关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据;它将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这增加了速度并提高了灵活性。
MySQL使用标准的SQL数据语言形式,主要包括DDL语句(数据定义语言)、DML语句(数据操作语句)和DCL语句(数据控制语句)。
二、DDL语句
对数据库内部的对象进行创建、删除、修改等操作的语言。它与DML语句的最大区别是:DML只是对表内部数据操作,不涉及表的定义、结构的修改。
1、启动mysql服务器
mysql service start
mysql -uroot -p
然后输入密码,出现“mysql>”提示符。
- 命令的结束符:用‘;’或‘\g’结束
- 通过‘help’或‘\h’命令显示帮助内容
- 通过‘\c’命令清除命令行buffer
命令的结束符:
2、创建数据库
creat database dbname
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| TESTDB |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
6 rows in set (0.00 sec)
2、选择数据库
use dbname
mysql> use test1;
Database changed
mysql> show tables;
Empty set (0.01 sec)
3、删除数据库
drop database dbname;
mysql> drop database test1;
Query OK, 0 rows affected (0.00 sec)
4、创建表
create table tablename(column_name1 column_type1, column_name2 column_type2,...)
mysql> create table emp(ename varchar(10),hiredate date,sal decimal(10,2), deptno int(2));
Query OK, 0 rows affected (0.06 sec)
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
查看创建的SQL语句
mysql> show create table emp \G;
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`ename` varchar(10) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(10,2) DEFAULT NULL,
`deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
5、删除表
drop table emp;
6、修改表
(1)、修改表类型
alter table tablename modify col_name column_definition
该命令无法修改列名称
修改表emp的ename字段定义,将varchar(10)改为varchar(20):
mysql> alter table emp modify ename varchar(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
(2)、增加表字段
alter table tablename add column col_name column_definition
在表emp中新增字段age,类型为int(3):
mysql> alter table emp add column age int(3);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
(3)、删除表字段
alter table tablename drop column col_name
将字段age删除掉:
mysql> alter table emp drop column age;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
(4)、字段改名
alter table tablename change old_col_name column_definition
将age改名为age1,同时修改字段类型为int(4):
mysql> alter table emp change age age1 int(4);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
| age1 | int(4) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
(5)、修改字段排列顺序
alter table tablename add col_name1 col_type after col_name2
将新增字段birth date 加在ename之后:
mysql> alter table emp add birth date after ename;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
alter table tablename modify col_name col_type first
修改字段age,将它放在最前面:
mysql> alter table emp modify age int(3) first;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age | int(3) | YES | | NULL | |
| ename | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
(6)、更改表名
alter table tablename rename new_tablename
将表emp改名为emp1:
mysql> alter table emp rename emp1;
Query OK, 0 rows affected (0.03 sec)
mysql> desc emp;
ERROR 1146 (42S02): Table 'test1.emp' doesn't exist
mysql> desc emp1;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age | int(3) | YES | | NULL | |
| ename | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
三、DML语句
对数据库中表记录的操作,主要包括表记录的插入、更新、删除和查询。
1、插入记录
insert into tablename (field1, field2,…) values (value1, value2,…)
向表emp中插入记录:
mysql> insert into emp values('lisa','2003-02-01','3000',2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into emp (ename,hiredate,sal,deptno) values('zzx1','2000-01-01','2000',1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| lisa | 2003-02-01 | 3000.00 | 2 |
| zzx1 | 2000-01-01 | 2000.00 | 1 |
+-------+------------+---------+--------+
2 rows in set (0.00 sec)
只对表中ename和sal字段插入记录(未插入记录字段显示为NULL
):
mysql> insert into emp (ename,sal) values('dony',1000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| lisa | 2003-02-01 | 3000.00 | 2 |
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| dony | NULL | 1000.00 | NULL |
+-------+------------+---------+--------+
3 rows in set (0.00 sec)
一次性插入多条记录
insert into tablename tablename values (value11,value12,…),(value21,value22,…)
mysql> create table dept(deptno int(2),deptname varchar(10));
Query OK, 0 rows affected (0.05 sec)
mysql> desc dept;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deptno | int(2) | YES | | NULL | |
| deptname | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into dept values(1,'tech'),(2,'sale'),(5,'fin'),(5,'dept5'),(6,'dept6');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | tech |
| 2 | sale |
| 5 | fin |
| 5 | dept5 |
| 6 | dept6 |
+--------+----------+
5 rows in set (0.00 sec)
2、更新记录
update tablename set field1=value1, field2=value2 where condition
将表emp中ename为lisa的sal改为4000:
mysql> update emp set sal=4000 where ename='lisa';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| lisa | 2003-02-01 | 4000.00 | 2 |
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| dony | NULL | 1000.00 | NULL |
+-------+------------+---------+--------+
3 rows in set (0.00 sec)
同时更新多个表的数据:
update emp a, dept b set a.sal=a.sal*b.deptno, b.deptname = a.ename where a.deptno=b.deptno
用于同时修改表emp中的字段sal和表dept中的字段的deptname
3、删除记录
delete from tablename where condition
从表emp中删除ename为dony的记录
mysql> delete from emp where ename='dony';
Query OK, 1 row affected (0.01 sec)
mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| lisa | 2003-02-01 | 4000.00 | 2 |
| zzx1 | 2000-01-01 | 2000.00 | 1 |
+-------+------------+---------+--------+
2 rows in set (0.00 sec)
同时删除多个表的数据:
delete a, b from emp a, dept b where a.deptno = b.deptno and a.deptno =3;
同时删除表emp和表dept中deptno为3的记录,如果不加where条件,将会删除表中的所有记录。
4、查询记录
- 将表中全部记录查询出来:
select * from tablename - 查询不重复的记录:
select distinct deptno from emp; - 条件查询
select * from emp where deptno=1;
select * from emp where deptno=1 and sal < 3000; - 排序和限制
select * from emp order by sal;
如果排序字段的值一样,则这些字段相同的记录将会无序排序。
select * from emp order by deptno, sal desc;
先按deptno排序,然后sal 按从高到低排序;desc表示按字段进行降序排序,asc则为升序排序。
select * from emp order by sal limit 3;
显示按sal排序后的前3条记录。 - 聚合
常用聚合函数有sum、count(*)、max和min。
group by表示要进行分类聚合的字段。
with rollup表示是否对分类聚合后的结果进行汇总。
having表示对分类后的结果在进行条件过滤。
select count(1) from emp;
select deptno, count(1) from emp group by deptno;
select deptno, count(1) from emp group by deptno with rollup;
select deptno, count(1) from emp gourp by deptno having count(1)>1;
select sun(sal), max(sal), min(sal) from emp;
- 表连接
内连接选出两张表中互相匹配的记录
select ename, deptname from emp, dept where emp.deptno = dept.deptno;
外连接选出其他不匹配的记录
左连接:
select ename, deptname from emp left join dept on emp.deptno=dept.deptno;
右连接:
select ename, deptname form dept right join emp on dept.deptno=emp.deptno;
- 子查询
子查询关键字:in、not in、=、!=、exists、not exists等
select * from emp where deptno in (select deptno from dept);
或
select emp.* from emp, dept where emp.deptno=dept.deptno;
- 记录联合
mysql> select deptno from emp
-> union all
-> select deptno from dept;
mysql> select deptno from emp
-> union
-> select deptno from dept;
四、DCL语句
用于管理系统中的对象权限时使用。
grant select, insert on sakila.* to 'z1'@'localhost' identified by '123';
exit
use sakila
insert into emp values('bzshen','2005-04-01',3000,'3');