数据库操作
1、数据库操作
创建用create,删除用drop
# 创建数据库
CREATE DATABASE hsp_db01;
# 创建一个使用 utf8 字符集的 hsp_db02 数据库
CREATE DATABASE hsp_db02 CHARACTER SET utf8
# 创建一个使用 utf8 字符集,并带校对规则的 hsp_db03 数据库
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin
# 校对规则 utf8_bin 区分大小 默认 utf8_general_ci 不区分大小写
# 显示数据库语句
SHOW DATABASES
# 删除数据库指令
DROP DATABASE hsp_db01
DROP DATABASE[IF EXISTS] db_name
2、表操作
# 创建表
CREATE TABLE table_name
( field1 datatype,
field2 datatype,
field3 datatype
)character set 字符集 collate 校对规则 engine 存储引擎
CREATE TABLE class(
id INT PRIMARY key AUTO_INCREMENT,
stuname VARCHAR(10) NOT NULL DEFAULT '');
CREATE TABLE stu(
id INT PRIMARY key,
stuname VARCHAR(10) NOT NULL DEFAULT '',
classid INT,
FOREIGN KEY (classid) REFERENCES class(id));
# 修改表
添加列:ALTER TABLE table_name ADD ( column datatype ) ;
修改列:ALTER TABLE table_name MODIFY ( column datatype ) ;
删除列:ALTER TABLE table_name DROP (co1umn) ;
修改表字符集: ALTER TABLE table_name CHARACTER SET 字符集;
修改列名:ALTER TABLE worker01 CHANGE `name` user_name VARCHAR(32);
修改表名:RENAME TABLE table_name TO new_name;
查看表的结构:DESC table_name;
# 删除表
删除表:DROP TABLE table_name;
# 复制表
# 复制表结构:把emp表的结构(列),复制到my_table
CREATE TABLE my_table LIKE emp;
# 复制表的内容
INSERT INTO table_name (字段1 ,字段2)
SELECT 字段1 ,字段2 FROM table_name2
# 视图操作
CREATE VIEW view_name AS SELECT 语句
ALTER VIEW view_name AS SELECT语句
SHOW CREATE VIEW view_name // 查看创建视图的指令。
DROP VIEW view_name1 , view_name2;
UPDATE emp_view01 SET ename='jack' WHERE empno=7369;
3、数据操作
# 1、插入操作
INSERT INTO table_name(id,good_name) VALUES (10,'华为手机');
INSERT INTO table_name(id,good_name) VALUES (10,'华为手机'),(11, '小米手机');
# 2、修改操作
UPDATE table_name SET price = 5000;
UPDATE table_name SET price = 5000 WHERE phone_name = 'sanxing';
# 3、删除操作
DELETE FROM table_name WHERE where_definition;
# DELETE不能删除某一列的值,但是可以使用UPDATE语句设为NULL或者‘ ’
# 4、查询操作
SELECT [DISTINCT] * [column1 , column2 , column3...] FROM table_name;
总数:select count(field1) as totalcount from table1
求和:select sum(field1) as sumvalue from table1
平均:select avg(field1) as avgvalue from table1
最大:select max(field1) as maxvalue from table1
最小:select min(field1) as minvalue from table1
计算:select (field1+field2-field3) as minvalue from table1
# 如果SELECT语句同时包含有GROUP BY , HAVING , ORDER BY , LIMIT 顺序为就为如此。
SELECT [DISTINCT] emp_id as id, depart, job, salary FROM Emp
WHERE id > 100 GROUP BY depart HAVING AVG(salary) < 2000 ORDER BY id asc[desc] LIMIT 0 , 10;
# WHERE 子句
# LIKE操作符进行模糊查询
# %:表示0到多个任意字符
# _:表示单个任意字符
# 多表查询
# 笛卡尔积
SELECT * FROM student, phone;
# 单列子查询
SELECT ename, job , sal, deptno FROM emp
WHERE job IN (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
) AND deptno <> 10;
SELECT goods_id , ecs_goods.cat_id , goods_name , shop_price
FROM ecs_goods ,(
SELECT MAX(shop_price) AS max_price, cat_id
FROM ecs_goods goods1
GROUP BY cat_id ) temp
WHERE ecs_goods.cat_id = temp.cat_id AND shop_price = max_price
# 多列子查询
SELECT * FROM student
WHERE (chinese , math , english) = (
SELECT chinese , math , english
FROM student
WHERE `name` = '宋小江')
# 合并查询
SELECT * FROM emp WHERE sal > 2000
union all
SELECT * FROM emp WHERE deptno = 20;
# union all :该操作符用于取得两个结果的并集,并使用该操作符时,不会取消重复行。
# union :与union all 相同,但是会自动去掉结果中集中重复行。
# 左外连接:左侧的表完全显示
SELECT `name` , stu.id , grade
FROM stu LEFT JOIN exam
ON stu.id = exam.id;
# 右外连接:右侧的表完全显示。
SELECT `name` , stu.id , grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id;