目录
1.开始创建数据库、创建数据库中的表
#连接数据库
mysql -u root -p
#创建数据库并设置指定字符集和校对规则
create database <database_name> if not exist <database_name>
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_chinese_ci;
#查看数据库定义声明
SHOW CREATE DATABASE <database_name>
#查看数据库
show databases
#使用 LIKE 从句,查看与 test_db 完全匹配的数据库
SHOW DATABASES LIKE 'test_db'
SHOW DATABASES LIKE '%test%'
SHOW DATABASES LIKE 'db%'
#删除已创建的数据库
DROP DATABASE IF EXISTS <数据库名>
#当用 CREATE DATABASE 语句创建数据库之后,该数据库不会自动成为当前数据库,
#需要用 USE 来指定当前数据库
USE <数据库名>
#不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能
show engines
#设置存储引擎
SET default_storage_engine=< 存储引擎名 >
#指定数据库中创建数据表
USE test_db;
CREATE TABLE tb_emp1
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
#查看表结构
desc <table_name>
其中,各个字段的含义如下:
Null:表示该列是否可以存储 NULL 值。
Key:表示该列是否已编制索引。PRI 表示该列是表主键的一部分,UNI 表示该列是 UNIQUE 索引的一部分,MUL 表示在列中某个给定值允许出现多次。
Default:表示该列是否有默认值,如果有,值是多少。
Extra:表示可以获取的与给定列有关的附加信息,如 AUTO_INCREMENT 等。
1). 数值类型
2).小数类型
3).日期/时间类型
4).字符类型
5)二进制类型
2.关于表的一些操作
#显示创建表时的CREATE TABLE语句
SHOW CREATE TABLE <表名>
#修改table
#ALTER TABLE <表名> [修改选项]
#{ ADD COLUMN <列名> <类型>
#| CHANGE COLUMN <旧列名> <新列名> <新列类型>
#| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
#| MODIFY COLUMN <列名> <类型>
#| DROP COLUMN <列名>
#| RENAME TO <新表名> }
#其中COLUMN字段可以去掉
#添加字段
ALTER TABLE <表名>
ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名];
#修改字段数据类型
ALTER TABLE <表名>
MODIFY <字段名> <数据类型>;
#删除字段
ALTER TABLE <表名> DROP <字段名>;
#修改字段名称(新数据类型不能为空)
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
#修改表名
ALTER TABLE <旧表名> RENAME [TO] <新表名>;
#删除表
DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]
3.主键/外键/唯一约束/检查约束/默认值约束/非空约束
1.创建时添加主键/外键/唯一约束/检查约束/默认值约束/非空约束
CREATE TABLE tb
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
);
CREATE TABLE tb_emp6
(
id INT(11) PRIMARY KEY,
name VARCHAR(25) UNIQUE,#唯一约束的创建
deptId INT(11),
location VARCHAR(50) DEFAULT 'Beijing',#默认值约束
salary FLOAT,
name VARCHAR(22) NOT NULL,#非空约束
CONSTRAINT fk_emp_dept1
CHECK(salary>0 AND salary<100),#检查约束的创建
FOREIGN KEY(deptId) REFERENCES tb(id)#外键的创建
);
2.在创建表时设置复合主键
#PRIMARY KEY [字段1,字段2,…,字段n]
CREATE TABLE tb2
(
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(name,deptId)
);
3.在修改表时添加主键/外键/唯一约束
ALTER TABLE <数据表名> ADD PRIMARY KEY(<列名>);#主键
ALTER TABLE <数据表名> ADD CONSTRAINT <索引名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);#外键
ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);#唯一约束
ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)#检查约束
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;#默认值约束
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NOT NULL;#非空约束
4.外键/唯一约束/检查约束/默认值约束的删除
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;#外键
ALTER TABLE <表名> DROP INDEX <唯一约束名>;#唯一约束
ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;#检查约束
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;#默认值约束
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;#非空约束删除
注意:使用 show create table <表名> 可以获取约束信息
4.select
1).select中的 DISTINCT去重 以及选取指定行数 LIMIT
SELECT * FROM 表名;
SELECT < 列名 > FROM < 表名 >;
SELECT <字段名1>,<字段名2>,…,<字段名n> FROM <表名>;
SELECT DISTINCT <字段名> FROM <表名>;#去重
SELECT * FROM tb_students_info LIMIT 4;#显示前4行
SELECT * FROM tb_students_info LIMIT 3,5;#忽略前3,第4开始数总共5行
2).select中 ORDER BY
SELECT * FROM tb ORDER BY {<列名> | <表达式> | <位置>} [ASC|DESC]#ASC升序默认
SELECT name,height FROM tb ORDER BY height,name;#先按height再按name排序
SELECT name,height FROM tb ORDER BY height DESC,name ASC;#先按height降序排序再按name升序排序
3).WHERE 子句来指定查询条件
SELECT name,height FROM tb WHERE height=170;#{=|<|<=|>|>=|<=>|<>|!=}
SELECT * FROM tb WHERE age>21 AND height>=175;#
SELECT name FROM tb WHERE name LIKE 'T%';#如 Tea/Thread等T开头的
SELECT name FROM tb WHERE name LIKE '____y';#y前面多少个_就有多少个字母如chovy/creyy等
SELECT * FROM tb WHERE login_date<'2016-01-01';#以日期字段作为条件设置查询条件
SELECT * FROM tb WHERE login_date BETWEEN '2015-10-01' AND '2016-05-01';#顾名思义
4).INNER JOIN 子句来指定查询条件
通过在查询中设置连接条件的方式,来移除查询结果集中某些数据行后的交叉连接。
SELECT id,name,age,dept_name FROM tb1,tb2 WHERE tb1.dept_id=tb2.dept_id;
SELECT id,name,age,dept_name FROM tb1 INNER JOIN tb2 WHERE tb1.dept_id=tb2.dept_id;
5).OUTER JOIN
外连接更加注重两张表之间的关系。按照连接表的顺序,可以分为左外连接和右外连接。
左外连接结果集中的 NULL 值表示右表中没有找到与左表相符的记录。
右外连接以右表为基表。
#左连接
SELECT name,dept_name FROM tb1 s
LEFT OUTER JOIN tb2 d
ON s.dept_id = d.dept_id;
#右连接
SELECT name,dept_name
FROM tb1 s
RIGHT OUTER JOIN tb2 d
ON s.dept_id = d.dept_id;
6).子查询
#在 tb_departments 表中查询 dept_type 为 A 的学院 ID,
#并根据学院 ID 查询该学院学生的名字
SELECT name FROM tb_students_info
-> WHERE dept_id IN
-> (SELECT dept_id
-> FROM tb_departments
-> WHERE dept_type= 'A' );
#查询 tb_departments 表中是否存在 dept_id=1 的供应商,
#如果存在,就查询 tb_students_info 表中的记录
SELECT * FROM tb_students_info
-> WHERE EXISTS
-> (SELECT dept_name
-> FROM tb_departments
-> WHERE dept_id=1);
7).GROUP BY
GROUP BY { <列名> | <表达式> | <位置> } [ASC | DESC]
#根据 dept_id 对 tb_students_info 表中的数据进行分组,将每个学院的学生姓名显示出来
SELECT dept_id,GROUP_CONCAT(name) AS names
-> FROM tb_students_info
-> GROUP BY dept_id;
8).HAVING
#根据 dept_id 对 tb_students_info 表中的数据进行分组,并显示学生人数大于1的分组信息
SELECT dept_id,GROUP_CONCAT(name) AS names
-> FROM tb_students_info
-> GROUP BY dept_id
-> HAVING COUNT(name)>1;
9).正则表达式REGEXP
# tb_departments 表中,查询 dept_name 字段以字母“C”开头的记录
mysql> SELECT * FROM tb_departments
-> WHERE dept_name REGEXP '^C';
http://c.biancheng.net/view/2572.html
5.INSERT
注意:一下的插入都以此创建的table为例
mysql> CREATE TABLE tb_courses
-> (
-> course_id INT NOT NULL AUTO_INCREMENT,
-> course_name CHAR(40) NOT NULL,
-> course_grade FLOAT NOT NULL,
-> course_info CHAR(100) NULL,
-> PRIMARY KEY(course_id)
-> );
1.向表中全部字段插入值
INSERT INTO tb_courses
-> (course_id,course_name,course_grade,course_info)
-> VALUES(1,'Network',3,'Computer Network');
#插入顺序必须与定义时相同
INSERT INTO tb_courses
-> VLAUES(3,'Java',4,'Java EE');
2.向指定字段插入值
为表的指定字段插入数据,是在 INSERT 语句中只向部分字段中插入值,而其他字段的值为表定义时的默认值。
INSERT INTO tb_courses
-> (course_name,course_grade,course_info)
-> VALUES('System',3,'Operation System');
3.使用 INSERT INTO…FROM 语句复制表数据
#新建一个表
CREATE TABLE tb_courses_new
-> (
-> course_id INT NOT NULL AUTO_INCREMENT,
-> course_name CHAR(40) NOT NULL,
-> course_grade FLOAT NOT NULL,
-> course_info CHAR(100) NULL,
-> PRIMARY KEY(course_id)
-> );
#复制旧表到新建的表中
INSERT INTO tb_courses_new
-> (course_id,course_name,course_grade,course_info)
-> SELECT course_id,course_name,course_grade,course_info
-> FROM tb_courses;
6.UPDATE语句来修改、DELETE语句删除
1.修改/删除表中的数据
#整列设置为4
UPDATE tb_courses_new
-> SET course_grade=4;
#删除全部数据
DELETE FROM tb_courses_new;
2.根据条件/删除修改
UPDATE tb_courses_new
-> SET course_name='DB',course_grade=3.5
-> WHERE course_id=2;
DELETE FROM tb_courses
-> WHERE course_id=4;
7.视图
1).视图的创建与查询
CREATE VIEW <视图名> AS <SELECT语句>
#创建基于单表的视图
CREATE VIEW view_students_info
-> AS SELECT * FROM tb_students_info;
CREATE VIEW v_students_info
-> (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
-> AS SELECT id,name,dept_id,age,sex,height,login_date
-> FROM tb_students_info;
#创建基于多表的视图
mysql> CREATE VIEW v_students_info
-> (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
-> AS SELECT id,name,dept_id,age,sex,height,login_date
-> FROM tb_students_info,tb_department;
#查询视图
describe <视图名>
2).修改视图
某些视图是可更新的。也就是说,可以使用 UPDATE、DELETE 或 INSERT 等语句更新基本表的内容。对于可更新的视图,视图中的行和基本表的行之间必须具有一对一的关系。
还有一些特定的其他结构,这些结构会使得视图不可更新。更具体地讲,如果视图包含以下结构中的任何一种,它就是不可更新的:
聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
位于选择列表中的子查询。
FROM 子句中的不可更新视图或包含多个表。
WHERE 子句中的子查询,引用 FROM 子句中的表。
ALGORITHM 选项为 TEMPTABLE(使用临时表总会使视图成为不可更新的)的时候。
3).删除视图
DROP VIEW <视图名1> [ , <视图名2> …]
DROP VIEW IF EXISTS v_students_info;
8.创建函数
语法格式:
CREATE FUNCTION <函数名> ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … )
RETURNS <类型>
RETURN <函数主体>
1).创建并使用自定义函数
CREATE FUNCTION StuNameById()
-> RETURNS VARCHAR(45)
-> RETURN
-> (SELECT name FROM tb_students_info
-> WHERE id=1);
SELECT StuNameById();
2).修改自定义函数
自定义函数被创建后,一直保存在数据库服务器上以供使用,直至被删除。删除自定义函数的方法与删除存储过程的方法基本一样,可以使用 DROP FUNCTION 语句来实现。
3).删除自定义函数
DROP FUNCTION [ IF EXISTS ] <自定义函数名>
9.常用运算符
http://c.biancheng.net/view/2561.html