安装

sudo apt-get install mysql-server

启动服务

sudo service mysql start

创建数据库

create database test;

连接数据库

use test

创建表

1
2
3
4
5
CREATE TABLE employee (
id int(10),
name char(20),
phone int(12)
);
数据类型 大小(字节) 用途 格式
INT 4 整数
FLOAT 4 单精度浮点数
DOUBLE 4 双精度浮点数
ENUM 单选,比如性别 ENUM(‘a’,’b’,’c’)
SET 多选 SET(‘1’,’2’,’3’)
DATE 3 日期 YYYY-MM-DD
TIME 3 时间点或持续时间 HH:MM:SS
YEAR 1 年份值 YYYY
CHAR 0~255 定长字符串
VARCHAR 0~255 变长字符串
TEXT 0~65535 长文本数据

显示所有表

show tables

插入数据

1
2
3
INSERT INTO employee(id,name,phone) VALUES(01,'Tom',110110110);
INSERT INTO employee VALUES(02,'Jack',119119119);
INSERT INTO employee(id,name) VALUES(03,'Rose');

VARCHAR,TEXT,DATE,TIME,ENUM 等类型的数据也需要单引号修饰,而INT,FLOAT,DOUBLE` 等则不需要。

约束

约束类型: 主键 默认值 唯一 外键 非空
关键字: PRIMARY KEY DEFAULT UNIQUE FOREIGN KEY NOT NULL

主键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TABLE department
(
dpt_name CHAR(20) NOT NULL,
people_num INT(10) DEFAULT '10',
CONSTRAINT dpt_pk PRIMARY KEY (dpt_name)
);
CREATE TABLE employee
(
id INT(10) PRIMARY KEY,
name CHAR(20),
age INT(10),
salary INT(10) NOT NULL,
phone INT(12) NOT NULL,
in_dpt CHAR(20) NOT NULL,
UNIQUE (phone),
CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name)
);
CREATE TABLE project
(
proj_num INT(10) NOT NULL,
proj_name CHAR(20) NOT NULL,
start_date DATE NOT NULL,
end_date DATE DEFAULT '2015-04-01',
of_dpt CHAR(20) REFERENCES department(dpt_name),
CONSTRAINT proj_pk PRIMARY KEY (proj_num,proj_name)
);

select

全部数据

select * from employee

数字< > =

select * from employee where age >25

and && or

select * from employee where age>30 or age<30
select * from employee where age>30 and salary>2000

in && NO IN

select * from employee where in_dpt in ('dpt2','dpt3')

like

select * from employee where name like 'J%'

order by

select * from employee where age>30 order by salary
默认升序(ASC) 降序用DECS

函数

函数名: COUNT SUM AVG MAX MIN
作用: 计数 求和 求平均值 最大值 最小值

求和select SUM(salary) from employee where age>30;

as

修改输出列名字select MAX(salary) as 30岁以上最高薪 from employee where age>30;

此处修改的只是显示的名字,并不是修改了数据中列的名字

子查询

1
2
3
SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
WHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');

连接查询

1
2
3
4
5
6
7
8
9
10
SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;
SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;

修改表

重命名表名

1
2
3
4
5
RENAME TABLE 原名 TO 新名字;
ALTER TABLE 原名 RENAME 新名;
ALTER TABLE 原名 RENAME TO 新名;

删除表名

DROP TABLE 表名字

删除数据库

drop test