一 进入退出mysql
登录MYSQL
语法如下： mysql -u用户名 -p用户密码
例如:
用户名:root,密码:root.
键入命令mysql -u root -p， 回车后提示你输入密码，输入root，然后回车即可进入到mysql中了，mysql的提示符是：mysql>
登录成功后可以看到版本信息:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 72Server version: 5.5.40 MySQL Community Server (GPL) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
注意，如果是连接到另外的机器上，则需要加入一个参数-h机器IP
退出MYSQL
exit (回车)
二 操作数据库
登录到mysql中，然后在mysql的提示符下运行下列命令，每个命令以分号结束。
显示数据库列表:
show databases;
缺省有两个数据库：mysql和test。 mysql库存放着mysql的系统和用户权限信息，我们改密码和新增用户，实际上就是对这个库进行操作。
执行后,dos命令行如下所示:
+--------------------+| Database |+--------------------+| information_schema || db_android || mysql || performance_schema || test |+--------------------+
指定当前工作数据库
use 数据库名称;
use db_android;
指定数据后dos命令变化如下
mysql> use db_android; Database changed mysql>
修改数据库
alter database 数据库名称;
mysql> alter database db_android default character set gb2312; Query OK, 1 row affected (0.00 sec)
创建数据库:
create database 库名;
create database db_zxn if not exists;
创建成功:
mysql> create database db_zxn;Query OK, 1 row affected (0.00 sec)
删除数据库
drop database 库名;
drop database db_zxn if exists;
删除成功:
mysql> drop database db_zxn;Query OK, 0 rows affected (0.02 sec)
三 数据库中的表操作
显示指定库中的数据表：
use db_android; show tables;
执行后,dos命令行如下所示:
mysql> use db_android; Database changed mysql> show tables; +----------------------+| Tables_in_db_android |+----------------------+| tb_android |+----------------------+1 row in set (0.00 sec)
显示数据表的结构
describe 表名;或者desc 表名;
describe tb_android;
执行后,dos命令行如下所示:
mysql> describe tb_android; +--------------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment || cust_name | char(44) | NO | | NULL | | | cust_sex | char(1) | NO | | 0 | || cust_andress | char(44) | YES | | NULL | | | cust_contact | char(44) | YES | | NULL | |+--------------+----------+------+-----+---------+----------------+5 rows in set (0.02 sec)
建表
create table 表名(字段列表);
use db_android;create table tb_user( user_id int not null auto_increment, user_name char(50) not null, primary key(uset_id));
建表执行后,dos命令行如下所示:
mysql> use db_android; Database changed mysql> create table tb_user( -> user_id int not null auto_increment, -> user_name char(50) not null, -> primary key(user_id) -> ); Query OK, 0 rows affected (0.02 sec)
创建临时表
使用TEMPORARY
create temporary table
mysql> create temporary table tmb_user(id int not null auto_increment, -> name char(50) not null, -> sex char(50) not null, -> primary key(id)); Query OK, 0 rows affected (0.01 sec)
更新表增加列
alter table tb_android add column cust_age int not null default 0 after cust_sex;
更新后
mysql> alter table tb_android add column cust_age int not null default 0 after cust_sex; Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tb_android; +--------------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment || cust_name | char(44) | NO | | NULL | | | cust_sex | char(1) | NO | | 0 | || cust_age | int(11) | NO | | 0 | | | cust_andress | char(44) | YES | | NULL | || cust_contact | char(44) | YES | | NULL | | +--------------+----------+------+-----+---------+----------------+ 6 rows in set (0.01 sec)
删表
drop table 表名;
drop table tb_user;
删表执行后,dos命令行如下所示:
mysql> drop table tb_zxn; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +----------------------+| Tables_in_db_android |+----------------------+| tb_android || tb_product || tb_user |+----------------------+3 rows in set (0.00 sec)
修改表中列的名称
CHANGE[COLUMN]子句
mysql> alter table tb_android change column cust_andress cust_location char(50) null; Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
修改表中指定列的默认值
//增加默认值为beijing 的一列`cust_city`mysql> alter table tb_android add column cust_city char(50) null default "beijing" after cust_contact; Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0//展示表的结构mysql> desc tb_android; +---------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------+------+-----+---------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(44) | NO | | NULL | | | cust_sex | char(1) | NO | | 0 | | | cust_age | int(11) | NO | | 0 | | | cust_location | char(50) | YES | | NULL | | | cust_contact | char(44) | YES | | NULL | | | cust_city | char(50) | YES | | beijing | | +---------------+----------+------+-----+---------+----------------+7 rows in set (0.00 sec)//修改表中cust_city的默认值为shanghaimysql> alter table tb_android alter column cust_city set default 'shanghai'; Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0//再次展示表的结构mysql> desc tb_android; +---------------+----------+------+-----+----------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------+------+-----+----------+----------------+ | cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(44) | NO | | NULL | | | cust_sex | char(1) | NO | | 0 | | | cust_age | int(11) | NO | | 0 | | | cust_location | char(50) | YES | | NULL | | | cust_contact | char(44) | YES | | NULL | | | cust_city | char(50) | YES | | shanghai | | +---------------+----------+------+-----+----------+----------------+7 rows in set (0.01 sec)
修改指定列的数据类型
mysql> alter table tb_android modify column cust_sex int first; Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tb_android; +---------------+----------+------+-----+----------+----------------+| Field | Type | Null | Key | Default | Extra | +---------------+----------+------+-----+----------+----------------+ | cust_sex | int(11) | YES | | NULL | || cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(44) | NO | | NULL | || cust_age | int(11) | NO | | 0 | | | cust_location | char(50) | YES | | NULL | || cust_contact | char(44) | YES | | NULL | | | cust_city | char(50) | YES | | shanghai | |+---------------+----------+------+-----+----------+----------------+7 rows in set (0.01 sec)
删除表中指定的列
mysql> alter table tb_android drop column cust_contact; Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tb_android; +---------------+----------+------+-----+----------+----------------+| Field | Type | Null | Key | Default | Extra | +---------------+----------+------+-----+----------+----------------+ | cust_sex | int(11) | YES | | NULL | || cust_id | int(11) | NO | PRI | NULL | auto_increment | | cust_name | char(44) | NO | | NULL | || cust_age | int(11) | NO | | 0 | | | cust_location | char(50) | YES | | NULL | || cust_city | char(50) | YES | | shanghai | | +---------------+----------+------+-----+----------+----------------+ 6 rows in set (0.01 sec)
修改表名
将表名tb_android修改为tb_java.
alter table tb_android rename to tb_java; mysql> alter table tb_android rename to tb_java; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +----------------------+| Tables_in_db_android |+----------------------+| tb_java || tb_product || tb_user |+----------------------+3 rows in set (0.00 sec)
修改表名字2
将表tb_java修改为tb_code
mysql> rename table tb_java to tb_code; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +----------------------+| Tables_in_db_android |+----------------------+| tb_code || tb_product || tb_user |+----------------------+3 rows in set (0.00 sec)
清空删除表：
delete from 表名;
delete from tb_android; mysql> delete from tb_android; Query OK, 0 rows affected (0.00 sec)
查看表中指定列
mysql> show full columns from tb_code; +---------------+----------+-----------------+------+-----+----------+----------------+---------------------------------+---------+| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |+---------------+----------+-----------------+------+-----+----------+----------------+---------------------------------+---------+| cust_sex | int(11) | NULL | YES | | NULL | | select,insert,update,references | || cust_id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | || cust_name | char(44) | utf8_general_ci | NO | | NULL | | select,insert,update,references | || cust_age | int(11) | NULL | NO | | 0 | | select,insert,update,references | || cust_location | char(50) | utf8_general_ci | YES | | NULL | | select,insert,update,references | || cust_city | char(50) | utf8_general_ci | YES | | shanghai | | select,insert,update,references | |+---------------+----------+-----------------+------+-----+----------+----------------+---------------------------------+---------+6 rows in set (0.01 sec)
显示表中的记录
select * from 表名;
select * from tb_android;
四 MySql中索引
索引定义
索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。
例如：有一张person表，其中有2W条记录，记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。
如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。
如果有了索引，那么会将该Phone字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必在遍历2W条数据了。
MySQL中索引的特点
优点：
所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引
大大加快数据的查询速度
缺点：
1、创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
2、索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值
3、当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。
使用原则：
1、对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引，
2、数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
3、在一同值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。
索引的分类
索引我们分为四类来讲 单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引、
1.1、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。
1.1.1、普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
1.1.2、唯一索引：索引列中的值必须是唯一的，但是允许为空值，
1.1.3、主键索引：是一种特殊的唯一索引，不允许有空值。
1.2、组合索引
在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。这个如果还不明白，等后面举例讲解时在细说
1.3、全文索引
全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 ..." 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思，如果感兴趣进一步深入使用它，那么看下面测试该索引时，会给出一个博文，供大家参考。
1.4、空间索引
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。
在创建空间索引时，使用SPATIAL关键字。
索引操作
创建索引
创建表的时候创建索引
格式：CREATE TABLE 表名[字段名 数据类型] [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length]) [ASC|DESC]
mysql> create table tb_book(id int not null, -> name char(50) not null, -> authors char(50) not null, -> info char(50) null, -> comment char(50) null, -> year_publication year not null, -> index(year_publication)); Query OK, 0 rows affected (0.02 sec)
查看表的结构
mysql> desc tb_book; +------------------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra | +------------------+----------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | || name | char(50) | NO | | NULL | | | authors | char(50) | NO | | NULL | || info | char(50) | YES | | NULL | | | comment | char(50) | YES | | NULL | || year_publication | year(4) | NO | MUL | NULL | | +------------------+----------+------+-----+---------+-------+ 6 rows in set (0.02 sec)
虽然表中没数据，但是有EXPLAIN关键字，用来查看索引是否正在被使用，并且输出其使用的索引的信息。
mysql> explain select * from tb_book where year_publication = 1990; +----+-------------+---------+------+------------------+------------------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+------------------+------------------+---------+-------+------+-------+ | 1 | SIMPLE | tb_book | ref | year_publication | year_publication | 1 | const | 1 | |+----+-------------+---------+------+------------------+------------------+---------+-------+------+-------+1 row in set (0.00 sec)
上面显示了key为year_publication。说明使用了索引。
创建唯一索引
mysql> create table tb_person(id int not null,name char(50) not null,unique index uniqId(id)); Query OK, 0 rows affected (0.01 sec)
创建主键索引
声明的主键约束，就是一个主键索引
mysql> create table tb_man(id int not null auto_increment,name char(20) not null, -> primary key (id) -> ); Query OK, 0 rows affected (0.02 sec)
查看一张表中所创建的索引
mysql> show index from tb_book; +---------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+----------- ----+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comm ent |+---------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+----------- ----+| tb_book | 1 | year_publication | 1 | year_publication | A | 0 | NULL | NULL | | BTREE | | |+---------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+----------- ----+1 row in set (0.01 sec)
在已经存在的表上创建索引
格式：ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]
增加索引
mysql> alter table tb_book add index nameIndex(name(30)); Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0
使用CREATE INDEX创建索引格式：CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 索引名称 ON 表名(创建索引的字段名[length])[ASC|DESC]
mysql> create index authors on tb_book(authors); Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0
删除索引
删除book表中的名称为nameIndex的索引。
mysql> alter table tb_book drop index nameIndex;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0
删除book表中名为BkNameIdx的索引DROP INDEX 索引名 ON 表名；
mysql> drop index authors on tb_book;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0
五 插入数据
insert ...values语句
mysql> insert into tb_book values(1,'jiexiesheji','zhangsan','hehe','submit',1990); Query OK, 1 row affected (0.02 sec)
INSERT…SET语句
mysql> insert into tb_book set id = 3,name = '003',authors='zhangxn',info='wowowowo',comment='good',year_publication=1987; Query OK, 1 row affected (0.01 sec)
INSERT…SELECT语句
mysql> insert into tb_book select '5','005','zxn005','this is 5','this comment',1988 union all select 3 ,'003','zhangxn','wowowo','good',1987; Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from tb_book; +----+-------------+----------+-----------+----------------+------------------+| id | name | authors | info | comment | year_publication | +----+-------------+----------+-----------+----------------+------------------+ | 1 | jiexiesheji | zhangsan | hehe | submit | 1990 || 2 | xml | lisi | hahahah | this is a book | 1992 | | 3 | 003 | zhangxn | wowowowo | good | 1987 || 5 | 005 | zxn005 | this is 5 | this comment | 1988 | | 3 | 003 | zhangxn | wowowo | good | 1987 |+----+-------------+----------+-----------+----------------+------------------+5 rows in set (0.00 sec)
六 删除数据
DELETE语句删除一行或多行数据
mysql> delete from tb_book where info = 'wowowo'; Query OK, 1 row affected (0.00 sec)
七 修改数据
UPDATE语句修改更新一个表中的数据
mysql> update tb_book set authors = 'zxn003' where id = 3;
八 数据查询
SELECT语句结构
| 子句 | 说明 | 是否必须使用 |
|---|---|---|
| SELECT | 要返回的列或表达式 | 是 |
| FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
| WHERE | 行级过滤 | 仅在按组计算聚合时使用 |
| GROUP BY | 分组说明 | 仅在按组计算聚合时使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出排序顺序 | 否 |
| LIMIT | 要检索的行数 | 否 |
指定列的查询
mysql> select id,name from tb_book; +----+-------------+| id | name | +----+-------------+ | 1 | jiexiesheji || 2 | xml | | 3 | 003 || 5 | 005 | +----+-------------+ 4 rows in set (0.00 sec)
查询所有列方式一
mysql> select * from tb_book; +----+-------------+----------+-----------+----------------+------------------+| id | name | authors | info | comment | year_publication | +----+-------------+----------+-----------+----------------+------------------+ | 1 | jiexiesheji | zhangsan | hehe | submit | 1990 || 2 | xml | lisi | hahahah | this is a book | 1992 | | 3 | 003 | zxn003 | wowowowo | good | 1987 || 5 | 005 | zxn005 | this is 5 | this comment | 1988 | +----+-------------+----------+-----------+----------------+------------------+ 4 rows in set (0.00 sec)
查询指定列并且使用别名
mysql> select name as booknam from tb_book; +-------------+| booknam |+-------------+| jiexiesheji || xml || 003 || 005 |+-------------+4 rows in set (0.00 sec)
替换查询结果集中的数据
mysql> select cust_name,case when cust_sex = 1 then 'nan' -> else 'nv' -> end as 'xingbie' -> from tb_code; +-----------+---------+| cust_name | xingbie | +-----------+---------+ | zxn1 | nan || zxn2 | nan | | zxn3 | nan |+-----------+---------+3 rows in set (0.00 sec)
查询指定列并计算
mysql> select cust_name,cust_age + 10 from tb_code; +-----------+---------------+| cust_name | cust_age + 10 | +-----------+---------------+ | zxn1 | 21 || zxn2 | 21 | | zxn3 | 23 |+-----------+---------------+3 rows in set (0.01 sec)
聚合函数
说明
| 名称 | 说明 |
|---|---|
| count | 总数 |
| max | 求最大值 |
| min | 求最小值 |
| sum | 求和 |
| avg | 求平均数 |
| std | 求最标准值 |
| varinace | 求方差 |
| group_contact | 右一组列值合成的结果 |
| bit_and | 逻辑与 |
| bit_or | 逻辑或 |
| bit_xor | 逻辑与或 |
交叉连接
SELECT * FROM tbl1 CROSS JOIN tbl2;
内连接
mysql>SELECT* ->FROM tb_student INNER JOIN tb_score ->ON tb_student.studentNo=tb_score.studentNo
外连接
mysql>SELECT* ->FROM tb_studentLEFT JOINtb_score ->ON tb_student.studentNo=tb_score.studentNo;
比较运算符
| 名称 | 说明 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| <=> | 不会返回unknown |
expression [NOT] BETWEEN expression1 AND expression2
mysql> select * from tb_coder where age between 10 and 13; +----+------+------+------+--------------+| id | age | sex | name | city |+----+------+------+------+--------------+| 1 | 11 | 1 | zxn1 | bj || 2 | 11 | 1 | zxn2 | bj || 3 | 13 | 1 | zxn3 | bj || 5 | 10 | 1 | zxn5 | xingtai || 6 | 12 | 0 | zxn6 | shijiazhuang |+----+------+------+------+--------------+5 rows in set (0.00 sec)
枚举in查询数据
mysql> select * from tb_coder where id in (1,3,5); +----+------+------+------+---------+| id | age | sex | name | city |+----+------+------+------+---------+| 1 | 11 | 1 | zxn1 | bj || 3 | 13 | 1 | zxn3 | bj || 5 | 10 | 1 | zxn5 | xingtai |+----+------+------+------+---------+3 rows in set (0.01 sec)
判定空值查询数据
mysql> select * from tb_coder where name is null; +----+------+------+------+-------+| id | age | sex | name | city |+----+------+------+------+-------+| 9 | 19 | 1 | NULL | hebei |+----+------+------+------+-------+1 row in set (0.01 sec)
子查询结合in查询
查询薪水大于2000的人的信息.
mysql> select * from tb_coder where id in (select id from tb_coder_salary where salary >= 2000); +----+------+------+------+------+| id | age | sex | name | city |+----+------+------+------+------+| 1 | 11 | 1 | zxn1 | bj || 2 | 11 | 1 | zxn2 | bj || 3 | 13 | 1 | zxn3 | bj |+----+------+------+------+------+3 rows in set (0.00 sec)
结合GROUP BY子句与分组查询数据
从表tb_coder中获取一个结果集,要求该结果集中包含每个相同城市的人数.
mysql> select name ,age, city,count(*) as peopleNum -> from tb_coder -> group by city; +------+------+--------------+-----------+| name | age | city | peopleNum | +------+------+--------------+-----------+ | zxn7 | 17 | baixiang | 1 || zxn1 | 11 | bj | 3 | | NULL | 19 | hebei | 1 || zxn4 | 14 | shanghai | 1 | | zxn6 | 12 | shijiazhuang | 1 || zxn5 | 10 | xingtai | 2 | +------+------+--------------+-----------+ 6 rows in set (0.00 sec)
结合GROUP BY子句查询各年龄的人数
mysql> select age ,count(*) as num -> from tb_coder -> group by age; +------+-----+| age | num | +------+-----+ | 10 | 1 || 11 | 2 | | 12 | 1 || 13 | 1 | | 14 | 1 || 18 | 1 | | 19 | 2 |+------+-----+7 rows in set (0.00 sec)
结合GROUP BY子句查询多个条件分组
按照城市和年龄分组查询人数
mysql> select age,city,count(*) as num -> from tb_coder -> group by age,city; +------+--------------+-----+| age | city | num |+------+--------------+-----+| 10 | xingtai | 1 || 11 | bj | 3 || 12 | shijiazhuang | 1 || 14 | shanghai | 1 || 18 | xingtai | 1 || 19 | baixiang | 1 || 19 | hebei | 1 |+------+--------------+-----+7 rows in set (0.00 sec)
HAVING子句过滤分组查询
按照城市分组,查询相同城市人数小于2的结果集.
mysql> select city -> from tb_coder -> group by city -> having count(*) < 2; +--------------+| city |+--------------+| baixiang || hebei || shanghai || shijiazhuang |+--------------+4 rows in set (0.00 sec)
结合ORDER BY子句查询数据进行升序排序
查询按照age升序排列.
mysql> select * from tb_coder -> order by age asc; +----+------+------+------+--------------+| id | age | sex | name | city |+----+------+------+------+--------------+| 5 | 10 | 1 | zxn5 | xingtai || 1 | 11 | 1 | zxn1 | bj || 2 | 11 | 1 | zxn2 | bj || 3 | 11 | 1 | zxn3 | bj || 6 | 12 | 0 | zxn6 | shijiazhuang || 4 | 14 | 1 | zxn4 | shanghai || 8 | 18 | 1 | zxn8 | xingtai || 7 | 19 | 1 | zxn7 | baixiang || 9 | 19 | 1 | NULL | hebei |+----+------+------+------+--------------+9 rows in set (0.00 sec)
结合ORDER BY子句查询数据进行降序排序
查询按照age降序排列.
mysql> select * from tb_coder -> order by age desc; +----+------+------+------+--------------+| id | age | sex | name | city |+----+------+------+------+--------------+| 7 | 19 | 1 | zxn7 | baixiang || 9 | 19 | 1 | NULL | hebei || 8 | 18 | 1 | zxn8 | xingtai || 4 | 14 | 1 | zxn4 | shanghai || 6 | 12 | 0 | zxn6 | shijiazhuang || 1 | 11 | 1 | zxn1 | bj || 2 | 11 | 1 | zxn2 | bj || 3 | 11 | 1 | zxn3 | bj || 5 | 10 | 1 | zxn5 | xingtai |+----+------+------+------+--------------+9 rows in set (0.00 sec)
使用LIMIT子句限制查询行数
mysql> select * from tb_coder limit 5; +----+------+------+------+----------+| id | age | sex | name | city |+----+------+------+------+----------+| 1 | 11 | 1 | zxn1 | bj || 2 | 11 | 1 | zxn2 | bj || 3 | 11 | 1 | zxn3 | bj || 4 | 14 | 1 | zxn4 | shanghai || 5 | 10 | 1 | zxn5 | xingtai |+----+------+------+------+----------+5 rows in set (0.00 sec)
**使用offset查询指定偏移行数 **
查询从第5行开始之后的3条数据.
mysql> select * from tb_coder limit 3 offset 4; +----+------+------+------+--------------+| id | age | sex | name | city |+----+------+------+------+--------------+| 5 | 10 | 1 | zxn5 | xingtai || 6 | 12 | 0 | zxn6 | shijiazhuang || 7 | 19 | 1 | zxn7 | baixiang |+----+------+------+------+--------------+3 rows in set (0.00 sec)
九 视图用法
创建视图
CREATE VIEW创建视图
mysql> create view female_coder_view as select * from tb_coder where sex = 0 with check option; Query OK, 0 rows affected (0.01 sec)
创建可能存在的视图
mysql> create or replace view male_coder_view as select * from tb_coder where sex = 1 with check option; Query OK, 0 rows affected (0.02 sec)
查看视图定义
mysql> show create view age11_coder_view; +------------------+------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +------------------+------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------+----------------------+----------------------+ | age11_coder_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `age11_coder_view` AS select `tb_coder`.`id` AS `id`,`tb_coder`.`age` AS `age`,`tb_coder`.`sex` AS `sex`,`tb_coder`.`name` AS `name`,`tb_coder`.`city` AS `city` from `tb_coder` where (`tb_coder`.`age` = 11) WITH CASCADED CHECK OPTION | utf8 | utf8_general_ci | +------------------+------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------+----------------------+----------------------+1 row in set (0.01 sec)
修改视图定义
mysql> alter view age11_coder_view(age,name) as select age,name from tb_coder; Query OK, 0 rows affected (0.02 sec)
删除视图
使用DROP VIEW语句删除视图
mysql> drop view age11_coder_view ;Query OK, 0 rows affected (0.00 sec)
作者：宁_593066063
链接：https://www.jianshu.com/p/7b6fad5b3fa5
点击查看更多内容
为 TA 点赞
评论
共同学习，写下你的评论
评论加载中...
作者其他优质文章
正在加载中
感谢您的支持，我会继续努力的～
扫码打赏，你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫，即可进行扫码打赏哦