Mysql

表结构的修改

首先创建一个数据表：

CREATE TABLE IF NOT EXISTS user2(
	id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
	username VARCHAR(20) NOT NULL UNIQUE,
	password CHAR(32) NOT NULL,
	email VARCHAR(50) NOT NULL DEFAULT '382',
	age VARCHAR(200) NOT NULL DEFAULT '北京',
	sex ENUM('男','女','保密') DEFAULT '保密',
	salary FLOAT(6,2),
	regTime INT UNSIGNED,
	face CHAR(100) NOT NULL DEFAULT 'default.jpg' ); 

mysql> DESC user2;
+----------+------------------------+------+-----+-------------+----------------+
| Field    | Type                   | Null | Key | Default     | Extra          |
+----------+------------------------+------+-----+-------------+----------------+
| id       | smallint(5) unsigned   | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)            | NO   | UNI | NULL        |                |
| password | char(32)               | NO   |     | NULL        |                |
| email    | varchar(50)            | NO   |     | 382         |                |
| age      | varchar(200)           | NO   |     | 北京        |                |
| sex      | enum('男','女','保密') | YES  |     | 保密        |                |
| salary   | float(6,2)             | YES  |     | NULL        |                |
| regTime  | int(10) unsigned       | YES  |     | NULL        |                |
| face     | char(100)              | NO   |     | default.jpg |                |
+----------+------------------------+------+-----+-------------+----------------+
9 rows in set

重命名数据表

mysql> --user2重命名为userx
方法一：ALTER TABLE user2 RENAME to userx;
方法二：RENAME TABLE userx to user2;

添加、删除字段

添加：ALTER TABLE tbl_name ADD 字段名称 字段类型  [完整性约束]  [first\AFTER 字段名称]

ALTER TABLE user2 ADD card CHAR(18)；
DESC user2;
+----------+------------------------+------+-----+-------------+----------------+
| Field    | Type                   | Null | Key | Default     | Extra          |
+----------+------------------------+------+-----+-------------+----------------+
| id       | smallint(5) unsigned   | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)            | NO   | UNI | NULL        |                |
| password | char(32)               | NO   |     | NULL        |                |
| email    | varchar(50)            | NO   |     | 382         |                |
| age      | varchar(200)           | NO   |     | 北京        |                |
| sex      | enum('男','女','保密') | YES  |     | 保密        |                |
| salary   | float(6,2)             | YES  |     | NULL        |                |
| regTime  | int(10) unsigned       | YES  |     | NULL        |                |
| face     | char(100)              | NO   |     | default.jpg |                |
| card     | char(18)               | YES  |     | NULL        |                |
+----------+------------------------+------+-----+-------------+----------------+
10 rows in set

删除：ALTER TABLE user2 
DROP  card;
……

修改字段

ALTER TABLE user2 MODIFY email VARCHAR(200) NOT NULL DEFAULT '690';

添加、删除默认值

添加：ALTER TABLE test ALTER age SET DEFAULT 18;
 desc test;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned | YES  |     | 18      |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set

--删除默认值
ALTER TABLE test ALTER email DROP DEFAULT;

添加主键

ALTER TABLE test ADD PRIMARY KEY(id)；
ALTER TABLE test DREP PRIMARY KEY;

插、更、删、查

插入数据

创建表：

CREATE TABLE IF NOT EXISTS user1(
	id TINYINT UNSIGNED AUTO_INCREMENT KEY,
	username VARCHAR(20)NOT NULL UNIQUE,
	password CHAR(32) NOT NULL,
	email VARCHAR(50) NOT NULL DEFAULT '7870@qq.com',
	age TINYINT UNSIGNED DEFAULT 18
	);

DESC user1;
+----------+---------------------+------+-----+-------------+----------------+
| Field    | Type                | Null | Key | Default     | Extra          |
+----------+---------------------+------+-----+-------------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL        |                |
| password | char(32)            | NO   |     | NULL        |                |
| email    | varchar(50)         | NO   |     | 7870@qq.com |                |
| age      | tinyint(3) unsigned | YES  |     | 18          |                |
+----------+---------------------+------+-----+-------------+----------------+
5 rows in set


INSERT INTO user1
 VALUES(1,'KING','KING','KING@QQ.COM',20);


SELECT * FROM user1
    -> ;
+----+----------+----------+-------------+-----+
| id | username | password | email       | age |
+----+----------+----------+-------------+-----+
|  1 | KING     | KING     | KING@QQ.COM |  20 |
+----+----------+----------+-------------+-----+
1 row in set
 

更新删除数据

查询语句

建立基础表（代码直接给出来）：

CREATE DATABASE IF NOT EXISTS cms DEFAULT CHARACTER SET utf8;
USE cms;
-- 管理员表cms_admin
CREATE TABLE cms_admin(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT 'admin@qq.com',
role ENUM('普通管理员','超级管理员') DEFAULT '普通管理员'
);
INSERT cms_admin(username,password,email,role) VALUES('admin','admin','admin@qq.com',2);

INSERT cms_admin(username,password) VALUES('king','king'),

('麦子','maizi'),

('queen','queen'),

('test','test');

-- 创建分类表cms_cate
CREATE TABLE cms_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(200) NOT NULL DEFAULT ''
);

INSERT cms_cate(cateName,cateDesc) VALUES('国内新闻','聚焦当今最热的国内新闻'),
('国际新闻','聚焦当今最热的国际新闻'),
('体育新闻','聚焦当今最热的体育新闻'),
('军事新闻','聚焦当今最热的军事新闻'),
('教育新闻','聚焦当今最热的教育新闻');

-- 创建新闻表cms_news
CREATE TABLE cms_news(
id INT UNSIGNED AUTO_INCREMENT KEY,
title VARCHAR(50) NOT NULL UNIQUE,
content TEXT,
clickNum INT UNSIGNED DEFAULT 0,
pubTime INT UNSIGNED,
cId TINYINT UNSIGNED NOT NULL COMMENT '新闻所属分类，对应分类表中的id',
aId TINYINT UNSIGNED NOT NULL COMMENT '哪个管理员发布的，对应管理员表中的id'
);
INSERT cms_news(title,content,pubTime,cId,aId) VALUES*

-- 创建身份表 provinces
CREATE TABLE provinces(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
proName VARCHAR(10) NOT NULL UNIQUE
);
INSERT provinces(proName) VALUES('北京'),
('上海'),
('深圳'),
('广州'),
('重庆');

-- 创建用户表cms_user
CREATE TABLE cms_user(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT 'user@qq.com',
regTime INT UNSIGNED NOT NULL,
face VARCHAR(100) NOT NULL DEFAULT 'user.jpg',
proId TINYINT UNSIGNED NOT NULL COMMENT '用户所属省份'
);

INSERT cms_user(username,password,regTime,proId)

VALUES('张三','zhangsan',1419811708,1),
('张三丰','zhangsanfeng',1419812708,2),
('章子怡','zhangsan',1419813708,3),
('long','long',1419814708,4),
('ring','ring',1419815708,2),
('queen','queen',1419861708,3),
('king','king',1419817708,5),
('blek','blek',1419818708,1),
('rose','rose',1419821708,2),
('lily','lily',1419831708,2),
('john','john',1419841708,2);

普通查询

-- 查询
SELECT * FROM cms_admin;

SELECT cms_admin.* FROM cms_admin;

-- 查询管理员编号和名称

SELECT id,username FROM cms_admin;

SELECT username,id,role FROM cms_admin;

-- 表来自于哪个数据库下db_name.tbl_name
SELECT id,username,role FROM cms.cms_admin;

-- 字段来自于哪张表

SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;

-- 给表名起别名

SELECT id,username FROM cms_admin AS a;

SELECT id,username FROM cms_admin a;

SELECT a.id,a.username,a.email,a.role FROM cms_admin AS a;

-- 给字段起别名

SELECT id AS '编号',username AS '用户名',email AS '邮箱',role '角色' FROM cms_admin;

SELECT a.id AS i,a.username AS u,a.email as e,a.role AS r FROM cms_admin AS a; 

SELECT id AS proId,proId AS id,username FROM cms_user;

SELECT 1,2,3,4,5,id,username FROM cms_user;

条件查询

-- WHERE条件
-- 查询编号为1的用户
SELECT id,username,email FROM cms_user WHERE id=1;

SELECT id,username,email FROM cms_user WHERE username='king';

-- 查询编号不为1的用户
SELECT  * FROM cms_user WHERE id!=1;

SELECT  * FROM cms_user WHERE id<>1;

-- 添加age字段
ALTER TABLE cms_user ADD age TINYINT UNSIGNED DEFAULT 18;

INSERT cms_user(username,password,regTime,proId,age)

VALUES('test1','test1',1419811708,1,NULL);

-- 查询表中记录age值为NULL
SELECT * FROM cms_user WHERE age=NULL;

SELECT * FROM cms_user WHERE age<=>NULL;

SELECT * FROM cms_user WHERE age<=>18;

-- IS NULL 或者IS NOT NULL
SELECT * FROM cms_user WHERE age IS NULL;

-- 查询编号在3~10之间的用户
SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;

-- 查询编号为1，3，5，7，9，11，13，100
SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,100,1000);

-- 查询proId为1 和3的用户

SELECT * FROM cms_user WHERE proId IN(1,3);

-- 查询用户名为king,queen,张三，章子怡的记录
SELECT * FROM cms_user WHERE username IN('king','queen','张三','章子怡');

SELECT * FROM cms_user WHERE username IN('KinG','QUEEN','张三','章子怡');

模糊查询

-- 模糊查询
-- %:代表0个一个或者多个任意字符
-- _:代表1个任意字符
-- 查询姓张的用户
SELECT * FROM cms_user WHERE username LIKE '张%';

-- 查询用户名中包含in的用户
SELECT * FROM cms_user WHERE username LIKE '%in%';

SELECT * FROM cms_user WHERE username LIKE '%';

-- 查询用户名为3位的用户

SELECT * FROM cms_user WHERE username LIKE '___';

--用户名_i%
SELECT * FROM cms_user WHERE username LIKE '_I%';

SELECT * FROM cms_user WHERE username LIKE 'king';

SELECT * FROM cms_user WHERE username NOT LIKE '_I%';

-- 查询用户名为king并且密码为king的用户
SELECT * FROM cms_user WHERE username='king' AND password='king';

-- 查询编号大于等于3的变量年龄不为NULL的用户

SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL;

-- 查询编号大于等于3的变量年龄不为NULL的用户 并且proId为的3
SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL AND proId=3;

-- 查询编号在5~10的用户并且用户名为4位的用户

SELECT * FROM cms_user WHERE id BETWEEN 5 AND 10 AND username LIKE '____';

-- 查询用户名以张开始或者用户所在身份为2,4的记录
SELECT * FROM cms_user WHERE username LIKE '张%' OR proId IN(2,4);

--查询proId，性别详情，注册时间详情，用户名详情 安装proId
SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime)
FROM cms_user GROUP BY proId;

UPDATE cms_user SET age=11 WHERE id=1;
UPDATE cms_user SET age=21 WHERE id=2;
UPDATE cms_user SET age=33 WHERE id=3;
UPDATE cms_user SET age=44 WHERE id=4;
UPDATE cms_user SET age=25 WHERE id=5;
UPDATE cms_user SET age=77 WHERE id=6;
UPDATE cms_user SET age=56 WHERE id=7;
UPDATE cms_user SET age=88 WHERE id=8;
UPDATE cms_user SET age=12 WHERE id=9;
UPDATE cms_user SET age=32 WHERE id=10;
UPDATE cms_user SET age=65 WHERE id=11;

--查询编号,sex,用户名详情以及组中总人数按照sex分组

SELECT id,sex,GROUP_CONCAT(username)AS users,COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;

-- 统计表中所有记录

SELECT COUNT(*) AS totalUsers FROM cms_user;

SELECT COUNT(id) AS totalUsers FROM cms_user;

--COUNT(字段)不统计NULL值
SELECT COUNT(age) AS totalUsers FROM cms_user;

--查询编号,性别,用户名详情，组中总人数，组中最大年龄，最小年龄，
-- 平均年龄，以及年龄总和按照性别分组

SELECT id,sex,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex;


-- WITH ROLLUP
SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age
FROM cms_user
GROUP BY sex WITH ROLLUP;


SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex WITH ROLLUP;

--查询性别sex,用户名详情,组中总人数，最大年龄，年龄总和,根据性别分组
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
GROUP BY sex;

-- 查询组中人数大于2的
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
GROUP BY sex
HAVING COUNT(*)>2;

-- 查询组中人数大于2并且最大年龄大于60的

SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60;

-- 查询编号大于等于2的用户
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
WHERE id>=2
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60;


SELECT id,sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
WHERE id>=2
HAVING COUNT(*)>2 AND MAX(age)>60;

-- 按照id降序排列DESC 默认的是ASC
SELECT * FROM cms_user ORDER BY id ;

SELECT * FROM cms_user ORDER BY id ASC;

SELECT * FROM cms_user ORDER BY id DESC;

-- 按照年龄升序排列
SELECT * FROM cms_user ORDER BY age ASC;

SELECT * FROM cms_user ORDER BY 1 DESC;

UPDATE cms_user SET age=12 WHERE id=5;

-- 按照年龄升序，id降序排列

SELECT * FROM cms_user ORDER BY age ASC,id DESC;

SELECT id,age,sex,GROUP_CONCAT(username),COUNT(*) AS totalUsers,SUM(age) AS sum_age 
FROM cms_user 
WHERE id>=2 
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC,id ASC;

-- 实现记录随机
SELECT * FROM cms_user ORDER BY RAND();

-- 查询表中前3条记录

SELECT * FROM cms_user LIMIT 3;

SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;

-- 查询表中前一条记录
SELECT * FROM cms_user LIMIT 1;

SELECT * FROM cms_user LIMIT 0,1;

SELECT * FROM cms_user LIMIT 1,1;

SELECT * FROM cms_user LIMIT 0,5;

SELECT id,sex,age,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
WHERE id>=1
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC
LIMIT 0,2;

连接查询

外键

Z子查询

正则查询

算术运算符

比较运算符

逻辑运算符

优先级

函数

字符串函数

日期函数

判断函数

信息函数

索引

管理