库管理
#1.建库：
mysql> CREATE DATABASE 清欢也野;
mysql> CREATE DATABASE 清欢也野 CHARSET utf8mb4 ;
#2.查库:
mysql> SHOW DATABASES;
#3.查看库定义
SHOW CREATE DATABASE 清欢也野;
#4.修改库定义：
mysql> alter database 清欢也野 charset=utf8;
#5.删除数据库：
mysql> drop database [if exists] 清欢也野;
mysql> drop database [if exists] 清欢也野;
表管理
create table job(
name varchar(20),
sex varchar(2),
age int(2),
hiredate date,
wage decimal(10,2));
#2.查看表
DESC job;
show create table job \G;
#3.修改表-添加列
alter table 表名 add column 列名 类型 [first|after 字段名];
alter table job add home varchar(30);
alter table job add birthday datetime after hiredate;
#4.修改表-修改列的类型或者约束
alter table 表名 modify column 列名 新类型 [新约束];
ALTER TABLE tb MODIFY [COLUMN] column_definition [FIRST | AFTER col_name];
alter table job modify name varchar(25);
#5.修改表-修改列名
alter table 表名 change column 旧列名 新列名 类型;
alter table job change wage salary decimal(10,2);
#6.删除列
alter table 表名 drop column 列名;
alter table job drop column home;
#7.修改表名
alter table 表名 rename [to] 新表名;
alter table job rename worker;
#8.删除表-可以binlog日志进行恢复
drop table [if exists] 表名;
#8.删除表-清空数据-无法恢复数据(TRUNCATE实际是删除原来的表并重新创建一个表)
truncate table job
#9.复制表-复制表的结构
create table 表名 like 旧表;
#10.复制表-复制表的结构和数据
create table 表名
select 查询列表 from 旧表 [where 筛选];
DQL
#1.全表查询
SELECT * FROM tablename;
select * from job;
#2.指定数据项,与Select * 等价 但是效率更高
select name,sex,age,hiredate,birthday,salary from job;
select * from job where age = 24;
select * from job where age >= 24 and salary > 8000;
#3.排序
SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC] ， field2 [DESC|ASC]，……fieldn [DESC|ASC]]
select * from job order by salary desc; # ASC 是默认的，不写就是升序（从小到大）
select * from job order by salary desc; # DESC 就是强制降序（从大到小）
#4.限制,显示一部分记录
select * from job limit 3;
select * from job order by salary desc limit 2; #先排序在取值，表示前两行
select * from job order by salary desc limit 2,3; #第二条到第三条记录
练习select
SELECT column1, column2 FROM 表 where column1="清欢也野" group by having
order by limit 10
练习order by
#语法
SELECT column1, column2,...
FROM tbl
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...
#按照prod_name 正序排序
mysql> select prod_name from products order by prod_name;
#desc 降序排序
mysql> select prod_name from products order by prod_name desc ;
#16进制排序
mysql>select prod_name,hex(prod_name) from products order by prod_name ;
#多字段asc排序
mysql> select prod_price,prod_name from products order by prod_price,prod_name ;
mysql> select prod_price,prod_name,hex(prod_price),hex(prod_name) from products order by prod_price,prod_name ;
#多个字段先正序,后降序
mysql> select prod_price,prod_name,hex(prod_price),hex(prod_name) from products order by prod_price asc,prod_name desc ;
练习Where
#语法
单表：select column_name1,column_name2... from table_name where column_name1='xxx' and .... or ..;
#1.查询单个值，等值查询
mysql> select prod_name,prod_price from products where prod_name='fuses';
#2.查询价格小于10美元的产品，范围大于
mysql> select prod_name,prod_price from products where prod_price < 10;
#3.查询价格小于等于10美元的产品
mysql> select prod_name,prod_price from products where prod_price <= 10;
#4.不匹配检查,列出不是由供应商1003制造的所有产品,要优化的
mysql> select vend_id,prod_name from products where vend_id <>1003;
mysql> select vend_id,prod_name from products where vend_id !=1003;
#5.范围值检查,检查某个范围区间的值,between...and,检索价格5美元和10美元之间的产品
mysql> select vend_id,prod_name,prod_price from products where prod_price between 5 AND 10;
#6.空值检查NULL,当一个字段无值(no value)，成为空值NULL，与0，空字符串或者空格不一样
mysql> select vend_id,prod_name,prod_price from products where prod_price IS NULL;
mysql> select cust_id from customers where cust_email is NOT NULL;
AND OR IN
select 品种,价格 from 表 where 品种='苹果' and 价格>10; # 查询苹果价格大于10的
select 品种,价格 from 表 where 品种='苹果' or 品种='梨';# 查询苹果或者是梨
select 品种,价格 from 表 where 品种 in (苹果,梨); # 查询苹果和梨
select 品种,价格 from 表 where 品种='苹果' and 品种='梨' OR 价格>10;# 查询苹果和梨，价格大于10的
通配符like过滤
_ 单字符匹配
select user,host from mysql.user where user like 'roo_%';
% 是通配符
select user,host from mysql.user where user like 'ro%';
完全过滤
select user,host from mysql.user where user='root';
内置函数concat
select concat(user,'@',host) from mysql.user where user='root';
内置函数count
SELECT COUNT(*) AS total_rows FROM test;
使用别名
SELECT CONCAT(user, '@', host) AS login
FROM mysql.user
WHERE user = 'root';
执行算术计算（+ - * / ）
create database test;
use test;
CREATE TABLE test (id INT,name VARCHAR(100), price INT);
INSERT INTO test (id, name, price) VALUES(1, '苹果', 10),(2, '香蕉', 20);
select name,price*5 as 'TOTLE' from test;
分组查询group by having
CREATE TABLE sales(id INT AUTO_INCREMENT PRIMARY KEY,product VARCHAR(50),qty INT);
INSERT INTO sales(product,qty)VALUES('苹果',10),('香蕉',20),('苹果',5),('梨',15),('香蕉',8),('苹果',7);
SELECT product AS 产品,COUNT(*) AS 销售次数 FROM sales GROUP BY product ORDER BY 销售次数 DESC;
解释：
SELECT product AS 产品 # 取出 product 字段，并在结果集中把列名显示为“产品”。
COUNT(*) AS 销售次数 # 对当前分组内的所有行计数，结果列名显示为“销售次数”。
FROM sales # 数据源是表 sales。
GROUP BY product # 按 product 列的值进行分组，每个不同的产品名形成一组。
ORDER BY 销售次数 DESC # | 按“销售次数”这一列降序排序，次数最多的排在最前面。
每条记录一小点~~
1.01^2==1.0201
浙公网安备 33010602011771号