mysql学习2

数据库表操作

DDL

数据库DDL操作

创建数据库：DDL
1. mysqladmin   -u root -p1  CREATE  db1
2. 直接去创建数据库目录并且修改权限
3. CREATE DATABASE 库名;
数据库命名规则：
    区分大小写
    唯一性
    不能使用关键字如 CREATE SELECT
    不能单独使用数字
查看数据库
SHOW DATABASES;                //查看所有数据库
SHOW CREATE DATABAESE 库名;	//查看创建的库信息
SELECT database();			//查看当前库
USE 数据库名		//切换数据库
SHOW 表名	//查看表	
DROP DATABASE 数据库名;	//删除数据库

表DDL操作

创建表 CREATE TABLE
语法：
CREATE TABLE 表名(
		字段名1  类型[(宽度) 约束条件],
		字段名2  类型[(宽度) 约束条件],
		字段名3  类型[(宽度) 约束条件]
)[存储引擎 字符集];
==在同一张表中，字段名是不能相同
==宽度和约束条件可选
==字段名和类型是必须的

查看表结构 desc TABLE,  show CREATE TABLE
SELECT id,name,sex,age from student1;	//查询表中指定字段的值
SELECT * from student1;					//查询表中所有字段的值

修改表 ALTER TABLE
ALTER TABLE t1  add math int(10);	//添加新字段
ALTER TABLE t1  modify chinese int(5) not null;	//修改字段数据类型、修饰符（约束）
ALTER TABLE t1 change chinese  china int(6);	//修改字段名称、数据类型、修饰符（约束）
ALTER TABLE student modify age bigint 		//修改表的字段类型和约束Run SQL
ALTER TABLE student CHANGE age number int	//修改表的字段约束类型和名字
ALTER TABLE student add id int first		//将字段添加到第一列
ALTER TABLE student ADD `like` varchar( 100)AFTER name	//将字段添加到某一列后面,关键字加反引号可做字段名
ALTER TABLE t1 drop en		//删除字段
insert into t1(id,name,math,china) values(1,"wing",80,90)		//插入数据(添加记录),字符串必须引号引起来
update t1 set name="lili" WHERE id=5		//更新记录 
delete from  t1 WHERE id=6;		//删除记录
delete from  t1;    //删除所有记录

复制表 CREATE TABLE ...
CREATE TABLE t10(SELECT * from t3);		//复制整张表
CREATE TABLE t10(SELECT id,name from t3);	//复制表结构

删除表 drop TABLE

修改表名称
rename TABLE emp to abc;
ALTER TABLE abc rename emp;

数据操作DML

在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括使用INSERT实现数据
的插入、DELETE实现数据的删除以及UPDATE实现数据的更新。
更新数据 insert
更新数据 update
删除数据 delete

一、插入数据INSERT
1. 插入完整数据（顺序插入）
	语法一：
	INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES (值1,值2,值3…值n);
	语法二：
	INSERT INTO 表名 VALUES (值1,值2,值3…值n);	#所有字段插入数据
2. 指定字段插入数据
	语法：
	INSERT INTO 表名(字段2,字段3…) VALUES (值2,值3…);
3. 插入多条记录
	语法：
	INSERT INTO 表名 VALUES
		(值1,值2,值3…值n),
		(值1,值2,值3…值n),
		(值1,值2,值3…值n);
		
4. 插入查询结果
	语法：
	INSERT INTO 表1(字段1,字段2,字段3…字段n) 
					SELECT (字段1,字段2,字段3…字段n) FROM 表2
					WHERE …

二、更新数据UPDATE
语法：
	UPDATE 表名 SET 字段1=值1,字段2=值2  WHERE CONDITION;
	
三、删除数据DELETE
语法：
	DELETE FROM 表名 
		WHERE CONITION;
示例：
	DELETE FROM mysql.user 
		WHERE authentication_string=’’;

DELETE from 表名 WHERE 条件;	
#注意：不加where 删除表中所有数据

数据库查询操作

单表查询

SELECT post FROM company;
SELECT DISTINCT post  FROM company;			//避免重复DISTINCT

通过四则运算查询
SELECT 437.4384/5
SELECT name, salary, salary*14 FROM company;
SELECT name, salary, salary*14 Annual_salary FROM company;

定义显示格式
   CONCAT() 函数用于连接字符串
SELECT CONCAT(name, ' annual salary: ', salary*14)  AS Annual_salary FROM company;

单条件查询
SELECT name from company WHERE salary=5000;

多条件查询
SELECT name from company WHERE salary>5000 and salary<6000;

关键字BETWEEN AND
SELECT name,salary FROM company WHERE salary BETWEEN 5000 AND 15000;
SELECT name,salary FROM company WHERE salary NOT BETWEEN 5000 AND 15000;

关键字IS NULL
SELECT name,job_description FROM company WHERE job_description IS NULL;
SELECT name,job_description FROM company WHERE job_description IS NOT NULL;

关键字IN集合查询
SELECT name, salary FROM company WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000;	
SELECT name, salary FROM company WHERE salary IN (4000,5000,6000,9000) ;
SELECT name, salary FROM company WHERE salary NOT IN (4000,5000,6000,9000) ;

排序查询
SELECT name from company order by name;
SELECT name from company order by name desc;
SELECT name from company order by name desc limit 3;		//限制次数
SELECT name from company order by name desc limit 1,3;

限制查询的记录数
SELECT * FROM company ORDER BY salary DESC LIMIT 5;			//默认初始位置为0 
SELECT * FROM company ORDER BY salary DESC LIMIT 3,5;	  //从第3条后面开始查询，显示5条	#从第4跳开始查询

分组查询
  GROUP BY和GROUP_CONCAT()函数一起使用
SELECT dep_id,GROUP_CONCAT(name) FROM company GROUP BY dep_id;
SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM company   GROUP BY dep_id;

函数
    count()		#统计多少行
    max()		#最大值
    min()		#最小值
    avg()		#平均
    database()
    user()
    now()
    sum()
    password()
SELECT COUNT(*) FROM company;
SELECT COUNT(*) FROM company WHERE dep_id=101;
SELECT MAX(salary) FROM company;
SELECT MIN(salary) FROM company;
SELECT AVG(salary) FROM company;
SELECT SUM(salary) FROM company;
SELECT SUM(salary) FROM company WHERE dep_id=101; 

多表查询

多表的连接查询
交叉连接：生成笛卡尔积，它不使用任何匹配条件  自己了解就好，这个生产用会把数据库跑死
两种方式：
    方式1：使用where条件 
    方式2：使用inner join

内连接：只连接匹配的行
SELECT employee6.emp_id,employee6.emp_name,employee6.age,department6.dept_name from employee6,department6 where employee6.dept_id = department6.dept_id;
#employee6.emp_id: 表名.字段
使用别名：
SELECT a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a,department6 b where a.dept_id = b.dept_id

外连接：
SELECT 字段列表 FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段
SELECT emp_id,emp_name,dept_name from  employee6 left join department6 on employee6.dept_id = department6.dept_id;
#(left join)左连接：会显示左边表内所有的值，不论在右边表内匹不匹配
SELECT emp_id,emp_name,dept_name from  employee6 right join department6 on employee6.dept_id = department6.dept_id;
#(right join)右连接：会显示右边表内所有的值，不论在左边表内匹不匹配
全外连接：包含左、右两个表的全部行
SELECT * from employee6 full  join department6

子查询
将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
还可以包含比较运算符：= 、 !=、> 、<等
1. 带IN关键字的子查询
查询employee表，但dept_id必须在department表中出现过
SELECT * FROM employee6 WHERE dept_id IN (SELECT dept_id FROM department6);

2. 带比较运算符的子查询
=、!=、>、>=、<、<=、<>
查询年龄大于等于25岁员工所在部门（查询老龄化的部门）
SELECT dept_id,dept_name FROM department6 WHERE dept_id IN (SELECT DISTINCT dept_id FROM employee6 WHERER age >= 25);

3. 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。
True或False，当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

department表中存在dept_id=203，Ture
SELECT * from employee6 WHERE EXISTS (SELECT * FROM depratment6 WHERE dept_id=203);

复合条件连接查询
以内连接的方式查询employee6和department6表，并且employee6表中的age字段值必须大于25
找出公司所有部门中年龄大于25岁的员工
select emp_id,emp_name,age,dept_name FROM employee6,department6 WHERE employee6.dept_id=department6.dept_id AND age > 25;

以内连接的方式查询employee6和department6表，并且以age字段的升序方式显示
SELECT emp_id,emp_name,age,dept_name FROM employee6,department6 where employee6.dept_id=depaartment6.dept_id ORDER BY age asc;