show databases;
操作演示:
create database 数据库名;
操作演示:
use 数据库名;
操作演示:
drop database 数据库名;
创建数据表:
create table 数据表名 (属性1 类型, 属性2 类型,);
类型后边可以加限定条件:primary key(主键)、not null(不能为空)、unique(唯一的)、foreign key(外键)、default(定义初值)、
drop table 数据表名;
alter table 修改表名,修改字段数据类型,条件约束...等等
1.修改表名:alter table 旧表名 rename 新表名;
2.修改字段的数据类型:alter table 表名 modify 属性名 数据类型;
3.修改字段名:alter table 表名 change 旧属性名 新属性名 新数据类型;
4.增加字段:alter table 表名 add 属性1 数据类型 (完整约束)first | after 属性名2; 若不设置位置,默认加到最后的位置
5.删除字段:alter table 表名 drop 属性名;
6.修改字段排列位置:alter table 表名 modify 属性1 数据类型 first | after 属性名2;
7.修改数据引擎:alter table 表名 engine = 储备引擎名;
8.删除表的外键约束:alter table 表名 drop foreign key 外键别名;
9.删除表的主键约束:alter table 表名 drop primary key;
10.删除表:drop table (if exists) 表名;
索引是由数据表中一列或多列组合而成,作用提高对表中数据的查询速度。
优点:提高检索数据的速度,对于有依赖关系的父表和子表之间的联合查询,可以提高查询速度使用分组和排序子句进行查询时,节省查询中分组和排序时间。
缺点:创建和维护索引需要浪费时间,耗费时间数量随数据量增加而增加,索引需要占用物理空间,增加,删除,修改数据时,要动态维护索引
索引的创建方式:
create table 表名 (属性名 数据类型 [完整约束],属性名 数据类型 [完整约束],[unique] index 索引名(属性名 [(长度)] [asc | desc]);
1.普通索引:这是最基本的索引,它没有任何限制。
2.唯一性索引:它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式
3.全文索引:全文索引只设置在char、varchar、text类型字段上,查询数据量较大的字符串时可以提高查找效率
4.单列索引:在表中单个字段上创建索引
5.多列索引:在表中多个字段上创建一个索引
6.空间索引
在存在的表上创建索引
create [unique | fulltext | spatial] index 索引名 on 表名 (属性名 [(长度)] [asc | desc]);
alter table 创建索引
alter table 表名 add [unique | fulltext | spatial] index 索引名 (属性名 [(长度)] [asc | desc]);
删除索引
drop index 索引名 on 表名;
alter table 表名 drop index 索引名;
CREATE DATABASE IF NOT EXISTS king DEFAULT CHARACTER SET 'UTF8';
USE king;
CREATE TABLE IF NOT EXISTS user(
id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
age TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',
email VARCHAR(50) NOT NULL DEFAULT 'abc@qq.com' COMMENT '邮箱'
)ENGINE=INNODB CHARSET=UTF8;
insert [into] 表名[(字段名称,...)] {value|values}(值,...)
UPDATE 表名 SET 字段名称=值,字段名称=值[WHERE 条件]
如果不添加条件,整个表中的记录都会被更新
-- 测试更新语句
-- 修改第一个用户的信息 id=1
UPDATE user SET age=29 WHERE id=1;
-- 修改id=3的用户,username age email
UPDATE user SET age=47,email='lilys@qq.com',username='lilys' WHERE id=3;
-- 所有用户年龄+10
UPDATE user SET age=age+10;
-- 将id<=5的用户年龄改为-20,将邮箱改为默认值
UPDATE user SET age=age-20,email=DEFAULT WHERE id<=5;
DELETE FROM 表名[WHERE 条件]
如果不添加条件,表中所有记录都会被删除
-- 测试删除语句
-- 删除用户名为lilys
DELETE FROM user WHERE username='lilys';
-- 删除年龄为36的用户
DELETE FROM user WHERE age=36;
-- 删除表中所有记录
DELETE FROM user;
DELETE清空数据表的时候不会重置AUTO_INCREMENT的值,可以通过ALTER语句将其重置为1,详见如下:
清空表后再插入两条记录,发现id是从9开始增长的,
INSERT user VALUES(NULL,'queen',25,'queen@qq.com');
INSERT user VALUES(DEFAULT,'lily',26,'lily@qq.com');
先用下面的语句查看自增长值
SHOW CREATE TABLE user;
再次清空数据表
如何让自增长值从1开始:
ALTER TABLE user AUTO_INCREMENT=1;
TRUNCATE[TABLE] 表名;
这有两项功能:
一是清除表中所有记录
二是会重置AUTO_INCREMENT的值
SELECT 字段名称或表达式,... FROM 表名
[WHERE 条件]
[GROUP BY {字段名称|位置} HAVING 二次筛选]
[ORDER BY{字段名称|位置|表达式}[ASC|DESC]]
[LIMIT 限制结果集的显示条数]
整个语句的含义为:根据 WHERE 子句的条件表达式,从 FROM 子句指定的基本表中找出满足条件的记录,在按照 SELECT 子句中的目标列表达式,选出记录中的属性值形成结果表。
WHERE常用运算符:
模糊查询 LIKE 像 % (百分号) 任意个字符 通配符: _ (下划线) 任意一个字符
再建一张user1表
-- 测试查询操作
CREATE TABLE user1(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号',
age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
addr VARCHAR(20) NOT NULL DEFAULT '北京',
married TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未结婚,1代表已婚',
salary FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE=INNODB CHARSET=UTF8;
INSERT user1 VALUES(1,'king',23,'男','北京',1,50000);
INSERT user1(username,age,sex,addr,married,salary) VALUES('queen',27,'女','上海',0,25000);
INSERT user1 SET username='imooc',age=31,sex='女',addr='北京',salary=40000;
INSERT user1 VALUES(NULL,'张三',38,'男','上海',0,15000),
(NULL,'张三风',38,'男','上海',0,15000),
(NULL,'张子怡',39,'女','北京',1,85000),
(NULL,'汪峰',42,'男','深圳',1,95000),
(NULL,'刘德华',58,'男','广州',0,115000),
(NULL,'吴亦凡',28,'男','北京',0,75000),
(NULL,'奶茶妹',18,'女','北京',1,65000),
(NULL,'刘嘉玲',36,'女','广州',0,15000);
-- 查询表中所有记录
SELECT * FROM user1;
-- username,addr,age
SELECT username,addr,age FROM user1;
-- 查询king数据库下user1表中的所有记录
SELECT * FROM king.user1;
-- 查询user1表中的id 编号 username 用户名 sex 性别
SELECT id AS '编号',username AS '用户名', sex AS '性别'
FROM user1;
SELECT id age,age id,username FROM user1;
-- 给表起别名
SELECT id,username FROM user1 AS u;
-- 测试表名.字段名
SELECT user1.id,user1.username,user1.age FROM user1 ;
SELECT u.id,u.username,u.addr,u.sex FROM user1 AS u;
-- 测试WHERE 条件的比较运算符
-- 查询id,username,age id=5的用户
SELECT id,username,age FROM user1
WHERE id=5;
SELECT id,username,age FROM user1
WHERE id=50;
-- 添加desc字段 VARCHAR(100)
ALTER TABLE user1
ADD userDesc VARCHAR(100);
-- 更新id<=9的用户 userDesc='this is a test'
UPDATE user1 SET userDesc='this is a test'
WHERE id<=9;
-- 查询用户userDesc 为NULL的用户
SELECT id,username,age,userDesc FROM user1
WHERE userDesc=NULL;
-- 检测NULL值
SELECT id,username,age,userDesc FROM user1
WHERE userDesc<=>NULL;
-- IS [NOT] NULL检测NULL值
SELECT id,username,age,userDesc FROM user1
WHERE userDesc IS NULL;
查询NULL值用下面这种方法查询不出来,要用别的方法查询。
-- 测试范围BETWEEN AND
-- 查询年龄在18~30之间的用户
SELECT id,username,age,sex FROM user1
WHERE age BETWEEN 18 AND 30;
-- 查询薪水在10000~50000之间的用户
SELECT id,username,age,salary FROM user1
WHERE salary BETWEEN 10000 AND 50000;
SELECT id,username,age,salary FROM user1
WHERE salary NOT BETWEEN 10000 AND 50000;
-- 测试指定集合 IN
-- 查询编号为1,3,5,7,9
SELECT id,username,age FROM user1
WHERE id IN(1,3,5,7,9,29,45,78);
SELECT id,username,age FROM user1
WHERE username IN('king','queen','lily','rose');
-- 测试逻辑运算符
-- 查询性别为男并且年龄>=20的用户
SELECT id,username,age,sex FROM user1
WHERE sex='男' AND age>=20;
-- id>=5 && age<=30
SELECT id,username,age,sex FROM user1
WHERE id>=5 AND age<=30;
SELECT id,username,age,sex FROM user1
WHERE id>=5 AND age<=30 AND sex='男';
-- 要求sex='女' 并且 addr='北京'
SELECT id,username,age,sex,addr FROM user1
WHERE sex='女' AND addr='北京';
-- 查询薪水范围在60000~10000并且性别为男 addr='北京'
SELECT id,username,age,sex,salary,addr FROM user1
WHERE salary BETWEEN 60000 AND 100000 AND sex='男' AND addr='北京';
-- 查询id=1 或者 用户名为queen
SELECT id,username,age FROM user1
WHERE id=1 OR username='queen';
-- 测试模糊查询
SELECT id,username,age FROM user1
WHERE username='king';
SELECT id,username,age FROM user1
WHERE username LIKE 'king';
-- 要求用户名中包含三
SELECT id,username,age,sex FROM user1
WHERE username LIKE '%三%';
-- 用户名中包含in
SELECT id,username,age FROM user1
WHERE username LIKE '%in%';
-- 要求查询出姓张的用户
SELECT id,username,age FROM user1
WHERE username LIKE '张%';
-- 查询以风结尾的用户
SELECT id,username,age FROM user1
WHERE username LIKE '%风';
-- 用户名长度为三位的用户
SELECT id,username,age,sex FROM user1
WHERE username LIKE '___';
SELECT id,username,age,sex FROM user1
WHERE username LIKE '张_';
SELECT id,username,age,sex FROM user1
WHERE username LIKE '张_%';
-- 测试分组
-- 按照性别分组sex
SELECT id,username,age,sex FROM user1
GROUP BY sex;
-- 按照addr分组
SELECT username,age,sex,addr FROM user1
GROUP BY addr;
-- 按照性别分组,查询组中的用户名有哪些
SELECT GROUP_CONCAT(username),age,sex,addr FROM user1
GROUP BY sex;
SELECT GROUP_CONCAT(username),age,sex,GROUP_CONCAT(addr) FROM user1 GROUP BY sex;
-- 测试COUNT()
SELECT COUNT(*) FROM user1;
SELECT COUNT(id) FROM user1;
-- 按照addr分组,得到用户名的详情,总人数,得到组中年龄的总和,年龄的最大值、最小值、平均值和
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
GROUP BY addr;
-- 按照sex分组,统计组中总人数、用户名详情,得到薪水总和,薪水最大值、最小值、平均值
SELECT sex,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY sex;
SELECT GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY sex
WITH ROLLUP;
-- 按照字段的位置来分组
SELECT id,sex,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY 2;
-- 查询age>=30的用户并且按照sex分组
SELECT sex,GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
WHERE age>=30
GROUP BY sex;
-- 按照addr分组,统计总人数
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr;
-- 对于分组结果进行二次筛选,条件是组中总人数>=3
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr
HAVING COUNT(*)>=3;
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr
HAVING totalUsers>=3;
-- 按照addr分组,
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY addr;
-- 要求平均薪水>=40000
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY addr
HAVING avg_salary>=40000;
-- 测试排序
-- 按照id降序排列
SELECT id,username,age
FROM user1
ORDER BY id DESC;
-- 按照age升序
SELECT id,username,age
FROM user1
ORDER BY age ;
-- 按照多个字段排序
SELECT id,username,age
FROM user1
ORDER BY age ASC,id ASC;
-- 测试条件+排序
SELECT id,username,age
FROM user1
WHERE age>=30;
SELECT id,username,age
FROM user1
WHERE age>=30
ORDER BY age DESC;
-- 实现随机记录
SELECT id,username,age
FROM user1
ORDER BY RAND();
-- 测试LIMIT语句
-- 显示结果集的前5条记录
SELECT id,username,age,sex
FROM user1
LIMIT 5;
SELECT id,username,age,sex
FROM user1
LIMIT 0,5;
-- 显示前3条记录
SELECT id,username,age,sex
FROM user1
LIMIT 0,3;
SELECT id,username,age,sex
FROM user1
LIMIT 3,3;
-- 更新前3条记录,将age+5
UPDATE user1 SET age=age+5 LIMIT 3;
-- 按照id降序排列,更新前三条记录,将age-10
UPDATE user1 SET age=age-10 ORDER BY id DESC LIMIT 3;
-- 删除前三条记录
DELETE FROM user1
LIMIT 3;
DELETE FROM user1
ORDER BY id DESC
LIMIT 3;
-- 测试完整SELECT 语句的形式
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr;
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=2;
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=1
ORDER BY totalUsers ASC;
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=1
ORDER BY totalUsers ASC
LIMIT 0,2;
我的博客园地址:https://www.cnblogs.com/zyx110/
转载请说明出处