MySql基础
数据库基本操作
- 创建数据库
CREATE DATABASE mydata
- 查看数据库信息
SHOW CREATE DATABASE mydata;
+------------+-------------------------------------------------------------------+
| Database | Create Database |
|------------+-------------------------------------------------------------------|
| mydata | CREATE DATABASE `mydata` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+------------+-------------------------------------------------------------------+
- 修改数据库信息
ALTER DATABASE mydata DEFAULT CHARACTER SET utf8;
- 删除数据库操作
DROP DATABASE mydata;
表基本操作
- 创建表
CREATE TABLE student(
id INT(10) NOT NULL,
name VARCHAR(10),
grade FLOAT
)
- 查看表
- SHOW CREATE TABLE
SHOW CREATE TABEL student;
+---------+----------------+
| Table | Create Table |
|---------+----------------|
| student | CREATE TABLE `student` (
`id` int(10) NOT NULL,
`name` varchar(12) DEFAULT NULL,
`grade` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------+
1 row in set
Time: 0.006s
- DESCRIBE
DESC student
+---------+-------------+--------+-------+-----------+---------+
| Field | Type | Null | Key | Default | Extra |
|---------+-------------+--------+-------+-----------+---------|
| id | int(10) | NO | PRI | <null> | |
| name | varchar(12) | YES | | <null> | |
| grade | float | YES | | <null> | |
+---------+-------------+--------+-------+-----------+---------+
3 rows in set
Time: 0.002s
- 修改表
- 修改表名
ALTER TABLE student RENAME [TO] students
- 修改字段名
ALTER TABLE [表名] CHANGE [旧字段名] [新字段名] [新数据类型]
- 修改字段的数据类型
ALTER TABLE [表名] MODIFY [字段名] [数据类型]
- 添加字段
ALTER TABLE [表名] ADD [字段名] [数据类型]
- 删除字段名
ALTER TABLE [表名] DROP [字段名]
- 修改字段的排列位置
ALTER TABLE [表名] MODIFY [字段名1] [数据类型] [FIRST|AFTER] [字段名2]
- 删除数据表
- DROP
将表格直接删除
- DROP
DROP TABLE [表名]
- DELETE
- TRUNCATE
delete from 表名;
truncate table 表名;
效率上truncate比delete快,但truncate删除后不记录mysql日志,不可以恢复数据。
delete的效果有点像将mysql表中所有记录一条一条删除到删完,delete删除后会在SQL日志中记录,当下次
增加数据时会从删除时的记录开始增加(如果主键是自增的话)
而truncate相当于保留mysql表的结构,重新创建了这个表,所有的状态都相当于新表。
增删改
- 增加数据
- INSERT INTO [表] (字段1,字段2…) VALUES(值1,值2…)
INSERT INTO students (id,name ,grade) VALUES (1,'刘石尧',100);
- INSERT INTO [表] SET 字段1=值1,字段2=值2…
INSERT INTO students SET id=4,name='111'
- 更新数据
- UPDATE [表] SET [字段1=值1] [WHERE 条件表达式]
UPDATE students SET name="000" WHERE id =1
查询
查询在数据库应用中具有重要的地位,内容也比较多。
单表查询
- SELECT
SELECT [DISTINCT] *|{字段名1,字段名2,...}
FROM 表名
[WHERE 条件表达式]
[GROUP BY 字段名[HAVING 条件表示式2]]
[ORDER BY 字段名 [ASC|DESC]]
[LIMIT [OFFSET]记录数]
按条件查询
- 带关系运算符查询
SELECT *FROM students WHERE id>1
- IN
判断某个字段的值是否在指定集合中,可以使用IN(或者NOT IN)
SELECT *FROM students WHERE id IN (1,2,3)
- BETWEEN AND
判断某个字段的值是否在指定的范围之内(或者NOT)BETWEEN 值1 ADN 值2
SELECT *FROM students where id NOT BETWEEN 1 and 3
- NULL查询
判断某些列的值是否为NULL
SELECT *FROM students WHERE grade IS NOT NULL
- DISTINCT
过滤重复的值
SELECT DISTINCT name FROM students;
作用于多个字段
SELECT DISTINCT name,grade FROM students;#当两个字段的值都相同的时候才被认为是重复数据
- LIKE
进行模糊查询,
%-匹配任意长度的字符串,包括空字符串
_-匹配单个字符
SELECT *FROM students WHERE name LIKE '%s%';#匹配包含s的所有字符串
SELECT *FROM students WHERE name LIKE '_2_';#匹配中间字符为2且字符串总数为3的字符串
AND
满足所有条件
OR
满足任意一个条件
如果AND和OR一起使用,AND的优先级高于OR
聚合函数
对一组数据进行统计,并返回为唯一值,称为聚合函数
- COUNT()
用于统计记录条数
SELECT COUNT(*) FROM students
- SUM()
求和函数,用于统计某个字段所有值的总和
SELECT SUM(grade) FROM students
- AVG()
求某个字段所有值的平均值
SELECT AVG(grade) FROM students
- MAX()
用于求出某个字段所有值中的最大值
SELECT MAX(grade) FROM students
- MIN()
用于求出某个字段所有值中的最小值
SELECT MIN(grade) FROM students
对查询结果进行排序
- ORDER BY
ORDER BY 字段名 [ASC|DESC]
SELECT *FROM students ORDER BY grade
分组查询
- GROUP BY
按某个字段或者多个字段中的值进行分组,字段中的值相同的为一组
注:select中的字段必须是group by后面的字段或者使用聚合函数。
SELECT COUNT(*) FROM students GROUP BY name
select *from student group by birth #错误
HAVING关键字
HAVING关键字和WHERE关键字作用一样,都是用于设置条件表达式对查询结果的过滤,不同的是
WHERE后不可以接聚合函数,而HAVING可以。
SELECT grade FROM students GROUP BY grade HAVING SUM(grade) > 100
LIMIT
指定查询从那一条记录开始到哪一条记录结束
SELECT *FROM students LIMIT 1,2#从第二条记录开始,共查询两条记录
索引
提高表中数据的查找速度
普通索引
这是最基本的索引类型,而且它没有唯一性之类的限制。
CREATE INDEX <索引名> ON tablename;
ALTER TABLE talbename ADD INDEX[索引名字]
唯一性索引
这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。
CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表,例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
主键
主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。
如果你曾经用过AUTO_INCREMENT类型的列,你可能已经熟悉主键之类的概念了。
主键一般在创建表的时候指定,例如“CREATE TABLE tablename ( […], PRIMARY KEY (列的列表) ); ”。
但是,我们也可以通过修改表的方式加入主键,例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。
每个表只能有一个主键。
全文索引
单列索引和多列索引
空间索引
连接查询
交叉连接
返回的是被连接的两个表中所有数据行的笛卡尔积。
SELECT *FROM grade CROSS JOIN students
+------+----------+------+-----------+---------+-------+
| id | name | id | name | grade | gid |
|------+----------+------+-----------+---------+-------|
| 1 | 电子一班 | 1 | lsy | 90 | 1 |
| 2 | 电子二班 | 1 | lsy | 90 | 1 |
| 1 | 电子一班 | 2 | liushiyao | 90 | 2 |
| 2 | 电子二班 | 2 | liushiyao | 90 | 2 |
| 1 | 电子一班 | 3 | liu | 91 | 2 |
| 2 | 电子二班 | 3 | liu | 91 | 2 |
| 1 | 电子一班 | 4 | lu | 81 | 1 |
| 2 | 电子二班 | 4 | lu | 81 | 1 |
+------+----------+------+-----------+---------+-------+
内连接
查询满足条件的记录,和where相似
select students.name,students.id FROM students join grade on grade.id=students.gid
外连接
返回结果不仅包含满足条件的数据,而且还包含左表或者右表或者两个表中所有的数据。
LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录。
mysql> select * from A left join B on A.name = B.name;
RIGHT JOIN(右连接):与 LEFT JOIN 相反,取得右表(table2)完全记录,即是左表(table1)并无匹配对应记录。
mysql> select * from A right join B on A.name = B.name;
Full Join(全连接):全连接产生的所有记录(双方匹配记录)在表A和表B。如果没有匹配,则对面将包含null。
mysql> select * from A left join B on B.name = A.name
-> union
-> select * from A right join B on B.name = A.name;
注意:mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join.
CREATE TABLE `user` (
`uid` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`account` varchar(32) NOT NULL,
`passwd` varchar(32) NOT NULL,
`age` int(11) DEFAULT NULL,
`sex` tinyint(4) DEFAULT NULL,
`device_id` varchar(45) DEFAULT NULL COMMENT '设备ID',
`create_time` timestamp NULL DEFAULT NULL,
`update_time` timestamp NULL DEFAULT NULL,
`nick` varchar(32) DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
CREATE TABLE `user_info` (
`uid` int(11) NOT NULL,
`medical_history` varchar(1024) DEFAULT NULL,
`accention` varchar(1024) DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
select user.uid,account,age,sex,device_id,nick,medical_history,accention from user join user_info on user.uid=user_info.uid where user.uid=4
子查询
一个查询语句嵌套在另外一个查询语句内的查询
- 带IN关键字
SELECT students.name from students where id IN (SELECT level_id from `level` where id < 3)
- 带EXISTS
不返回任何数据,只返回TRUE或者FALSE
SELECT students.name from students where EXISTS (SELECT level_id from `level` where id < 3)
EXISTS关键字比IN关键字运行效率高,如果在实际开发中,特别是大数据量的时候,建议使用EXISTS。
- 带ANY
满足其中任意一个条件,就返回一个结果作为外层查询条件。
select students.name from students where gid< ANY (select level_id from `level`)
- 带ALL
同时满足所有内层查询条件
select students.name from students where gid< ALL (select level_id from `level`)