MySQL是结构化查询语言的缩写(Structure Query Language),它是关系型数据库的通用语言,非常强大,可以非常高效的进行数据库的增删改查操作,SQL+索引更是可以实现带各种附加条件的高效率查询操作
一、MySQL介绍
- MySQL目前属于Oracle甲骨文公司,大家熟悉的关系型数据库有微软的SQL Server,甲骨文的Oracle和MySQL
- MySQL分为企业版和社区版,其中社区版是完全免费并且开源的
- MySQL和其它关系型数据库有一个非常大的区别,就是支持可更换的插件式的存储引擎,其中InnoDB非常强大
- 目前goole、淘宝、百度、腾讯、新浪、facebook等大公司都在使用MySQL作为数据存储层方案
- MySQL设计成C/S客户端服务器模型,应用作为MySQL Client向MySQL Server发送请求,获取响应,因此MySQL非常适用于集群环境,方便做主从复制,读写分离操作
- 为了提高效率,MySQL Client和MySQL Server如果处在不同主机上,当然是通过Socket进行网络通信的;如果它们在同一台机器上,那么Client和Server之间是通过共享内存进行通信的,效率比Socket通信更高
- MySQL的服务器模块采用的是I/O复用+可伸缩的线程池,是实现网络高并发服务器的经典模型
二、MySQL的创建、修改、查询、删除
1、创建数据库:
约束条件 说明
PRIMARY KEY 修饰的属性为该表的主键,可以区分不同的行记录
FOREIGN KEY 修饰的属性为该表的外键,关联了父表的主键
NOT NULL 表示该属性不能为空
UNIQUE 表示该属性的值是唯一的
AUTO_INCREMENT MySQL特色,表示该属性是自增的,整数类型
DEFAULT 给属性设置默认值
在mysql中,创建数据库必须通过sql语句create database实现,在创建之前可以使用show语句来显示现在已经存在的数据库。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| donglin |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (1.82 sec)
mysql> create database student;
Query OK, 1 row affected (0.29 sec)
此时数据库student已经创建成功。
选择数据库:use student;
2、删除数据库:是指在数据库系统中删除已经存在的数据库。
其语法形式如下:
mysql> drop database student;
Query OK, 0 rows affected (0.37 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| donglin |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
然后用show查看已经没有student那个数据库了。
注意:删除数据库会删除数据库中所有的表和表中所有的数据,如果一定要删除某一个数据库,可以先将该数据库备份,然后再删除,可以避免不必要的麻烦。
3、修改数据库:如果表在使用的过程中,逐渐发现表的字段不满足设计要求,而且表中已添加大量数据,此时想添加新的字段或者删除已有的字段,可以通过alter命令操作。
// insert
insert into student values();
// delete
delete fron student where score < 60.0 or age < 20;
// update
update student set level=“合格” where score > 90.0;
4、查询数据库:
SQL的基本查询结构如下:
SELECT 属性列表
FROM 表名
[WHERE 条件表达式1]
[GROUP BY 属性名1 [HAVING 条件表达式2]]
[ORDER BY 属性名2 [ASC | DESC]]
可以用desc student;查看表student的结构。
查看表详细结构语句show create table student\G;
下面是几种常见的查询操作
1).带in的子查询
如:select * from user where id in(select stu_id from grade where average>=60.0);
2).内连接和外链接查询
内连接查询:select a.id,b.average,c.address from user a inner join grad b on a.id=b.userid inner join info c on a.id=c.userid where b.average>=60.0;
外连接查询
[左连接查询]
SELECT a.属性名列表, b.属性名列表 FROM table_name1 a [OUTER] LEFT JOIN table_name2 b on a.id = b.id;
[右连接查询]
SELECT a.属性名列表, b.属性名列表 FROM table_name1 a [OUTER] RIGHT JOIN table_name2 b on a.id = b.id;
一般都会用连接查询代替in子查询进行多表联合查询,子查询的效率远不及连接查询效率高!
3).分组查询
select name, count(*) from record group by id;
4)对结果排序
select * from user inner join grade on user.id = grade.id where average between 80.0 and 100.0 order by average desc;
下面是MySQL的一些基本操作
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| donglin |
| mysql |
| performance_schema |
| student |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use student;
Database changed
mysql> create table student(id int primary key auto_increment,
-> name varchar(20),
-> age tinyint,
-> sex enum('男', '女'),
-> score double,
-> level varchar(20) default '不合格');
Query OK, 0 rows affected (0.04 sec)
mysql> desc student;
+-------+-------------------+------+-----+-----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------+------+-----+-----------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('男','女') | YES | | NULL | |
| score | double | YES | | NULL | |
| level | varchar(20) | YES | | 不合格 | |
+-------+-------------------+------+-----+-----------+----------------+
6 rows in set (0.05 sec)
mysql> show create table student;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`sex` enum('男','女') DEFAULT NULL,
`score` double DEFAULT NULL,
`level` varchar(20) DEFAULT '不合格',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table student\g
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`sex` enum('男','女') DEFAULT NULL,
`score` double DEFAULT NULL,
`level` varchar(20) DEFAULT '不合格',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`sex` enum('男','女') DEFAULT NULL,
`score` double DEFAULT NULL,
`level` varchar(20) DEFAULT '不合格',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into student(name,age,sex,score) values('zhang san', 20, '男', 80.5);
Query OK, 1 row affected (0.05 sec)
mysql> select * from student;
+----+-----------+------+------+-------+-----------+
| id | name | age | sex | score | level |
+----+-----------+------+------+-------+-----------+
| 1 | zhang san | 20 | 男 | 80.5 | 不合格 |
+----+-----------+------+------+-------+-----------+
1 row in set (0.01 sec)
mysql> insert into student(name,age,sex,score) values('wang wu', 22, '女', 73.5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-----------+------+------+-------+-----------+
| id | name | age | sex | score | level |
+----+-----------+------+------+-------+-----------+
| 1 | zhang san | 20 | 男 | 80.5 | 不合格 |
| 2 | wang wu | 22 | 女 | 73.5 | 不合格 |
| 3 | liu shuo | 18 | 男 | 56.7 | 不合格 |
| 4 | zhao yun | 24 | 女 | 95.8 | 不合格 |
+----+-----------+------+------+-------+-----------+
4 rows in set (0.00 sec)
mysql> select * from student where score>60.0;
+----+-----------+------+------+-------+-----------+
| id | name | age | sex | score | level |
+----+-----------+------+------+-------+-----------+
| 1 | zhang san | 20 | 男 | 80.5 | 不合格 |
| 2 | wang wu | 22 | 女 | 73.5 | 不合格 |
| 4 | zhao yun | 24 | 女 | 95.8 | 不合格 |
+----+-----------+------+------+-------+-----------+
3 rows in set (0.00 sec)
mysql> select * from student where score>60.0 order by score;
+----+-----------+------+------+-------+-----------+
| id | name | age | sex | score | level |
+----+-----------+------+------+-------+-----------+
| 2 | wang wu | 22 | 女 | 73.5 | 不合格 |
| 1 | zhang san | 20 | 男 | 80.5 | 不合格 |
| 4 | zhao yun | 24 | 女 | 95.8 | 不合格 |
+----+-----------+------+------+-------+-----------+
3 rows in set (0.01 sec)
mysql> select * from student where score>60.0 order by score desc;
+----+-----------+------+------+-------+-----------+
| id | name | age | sex | score | level |
+----+-----------+------+------+-------+-----------+
| 4 | zhao yun | 24 | 女 | 95.8 | 不合格 |
| 1 | zhang san | 20 | 男 | 80.5 | 不合格 |
| 2 | wang wu | 22 | 女 | 73.5 | 不合格 |
+----+-----------+------+------+-------+-----------+
3 rows in set (0.00 sec
mysql> update student set level='合格' where score > 80.0;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from student;
+----+-----------+------+------+-------+-----------+
| id | name | age | sex | score | level |
+----+-----------+------+------+-------+-----------+
| 1 | zhang san | 20 | 男 | 80.5 | 合格 |
| 2 | wang wu | 22 | 女 | 73.5 | 不合格 |
| 3 | liu shuo | 18 | 男 | 56.7 | 不合格 |
| 4 | zhao yun | 24 | 女 | 95.8 | 合格 |
+----+-----------+------+------+-------+-----------+
4 rows in set (0.00 sec)
mysql> select * from student where level = '合格' group by sex;
+----+-----------+------+------+-------+--------+
| id | name | age | sex | score | level |
+----+-----------+------+------+-------+--------+
| 1 | zhang san | 20 | 男 | 80.5 | 合格 |
| 4 | zhao yun | 24 | 女 | 95.8 | 合格 |
+----+-----------+------+------+-------+--------+
2 rows in set (0.01 sec)
mysql> insert into student(name,age,sex,score,level) values('gao lu', 23, '女', 87.5, '合格');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student where level = '合格';
+----+-----------+------+------+-------+--------+
| id | name | age | sex | score | level |
+----+-----------+------+------+-------+--------+
| 1 | zhang san | 20 | 男 | 80.5 | 合格 |
| 4 | zhao yun | 24 | 女 | 95.8 | 合格 |
| 5 | gao lu | 23 | 女 | 87.5 | 合格 |
+----+-----------+------+------+-------+--------+
3 rows in set (0.00 sec)
mysql> select * from student where level = '合格' group by sex;
+----+-----------+------+------+-------+--------+
| id | name | age | sex | score | level |
+----+-----------+------+------+-------+--------+
| 1 | zhang san | 20 | 男 | 80.5 | 合格 |
| 4 | zhao yun | 24 | 女 | 95.8 | 合格 |
+----+-----------+------+------+-------+--------+
2 rows in set (0.00 sec)
mysql> select count(*) from student where level = '合格' group by sex;
+----------+
| count(*) |
+----------+
| 1 |
| 2 |
+----------+
2 rows in set (0.01 sec)
mysql> select count(*) as sexsroup from student where level = '合格' group by sex;
+----------+
| sexsroup |
+----------+
| 1 |
| 2 |
+----------+
2 rows in set (0.01 sec)
mysql> select count(*),sex as sexsroup from student where level = '合格' group by sex;
+----------+----------+
| count(*) | sexsroup |
+----------+----------+
| 1 | 男 |
| 2 | 女 |
+----------+----------+
2 rows in set (0.00 sec)