【MySQL】基本语句

MySql基础

数据库基本操作

1. 创建数据库

CREATE DATABASE mydata

1. 查看数据库信息

SHOW CREATE DATABASE mydata;

+------------+-------------------------------------------------------------------+
| Database   | Create Database                                                   |
|------------+-------------------------------------------------------------------|
| mydata     | CREATE DATABASE `mydata` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+------------+-------------------------------------------------------------------+

1. 修改数据库信息

ALTER DATABASE mydata DEFAULT CHARACTER SET utf8;

1. 删除数据库操作

DROP DATABASE mydata;

表基本操作

1. 创建表

CREATE TABLE student(
    id INT(10) NOT NULL,
    name VARCHAR(10),
    grade FLOAT
)

1. 查看表
   
   • 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

1. 修改表
   
   • 修改表名

 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]

1. 删除数据表
   
   • DROP
     

     将表格直接删除

DROP TABLE [表名]

• DELETE

可以指定某一个删除表中的数据；delete可以返回行数；可以与WHERE一起使用

• TRUNCATE

删除表中的所有数据，但不能和where一起用。不触发任何Delete触发器。 **delete和truncate的区别**

delete from 表名;
truncate table 表名;

效率上truncate比delete快，但truncate删除后不记录mysql日志，不可以恢复数据。
delete的效果有点像将mysql表中所有记录一条一条删除到删完，delete删除后会在SQL日志中记录，当下次
增加数据时会从删除时的记录开始增加（如果主键是自增的话）
而truncate相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。

增删改

1. 增加数据
   
   • 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'

1. 更新数据
   
   • 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%';#匹配包含ｓ的所有字符串
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`)