数据库操作总结

数据库操作

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;