MySql建库建表基础——随记1

1、数据表的本质仍然是文件

2、数据库语言分类

DDL：数据库定义语言：creat 数据库名/表名
DML：数据库操纵语言：增删改 insert、delete、update
DQL:数据库查询语言：select等
DCL：数据库控制语言：管理数据库，比如用户权限grant revoke等

数据库操作

1、创建数据库：

CREATE DATABASE test_01;
CREATE DATABASE youngDb_02 CHARACTER SET utf8;
CREATE DATABASE youngDb_03 CHARACTER SET utf8 COLLATE utf8_bin;

2、备份数据库：

D:\>mysqldump -u root -p -B youngdb_02 youngDb-03 >D:\\bak.sql

该命令会将数据库youngdb_02和 youngDb-03备份到D盘的bak.sql文件中去，该文件中就是可执行的SQL语句。

3、删除数据库：

drop DATABASE youngdb_03

4、恢复数据库：

注意：首先要进入Mysql命令行再执行。

mysql> source d:\\bak.sql

或者直接执行bak.sql中的SQL语句即可。

5、查看数据库

SHOW DATABASES
SHOW CREATE DATABASE test_01

数据库表的操作

1、创建数据库表

CREATE TABLE user1 (
										 id INT,
										 `name` VARCHAR(255),
										 `password` VARCHAR(255)
                  )
									CHARACTER SET utf8  COLLATE utf8_bin ENGINE INNODB

2、备份数据库的表

D:\>mysqldump -u root -p youngdb_02  t2 >D:\\t2bak.sql

该命令将数据库 youngdb_02中的t2这张表备份到了D盘下的t2bak.sql文件中。

3、删除数据库的表

DROP TABLE t2 

4、恢复数据库的表

先选择指定的数据库

mysql> use youngdb_02

再执行恢复命令

mysql> source  D:\\t2bak.sql

或者直接运行SQL文件即可。

5、修改表

-- 向user1表中的password列后面添加一个名为zhuming的列
ALTER TABLE user1 ADD zhuming VARCHAR ( 255 ) NOT NULL DEFAULT '' AFTER `PASSWORD`
-- 修改列
ALTER TABLE user1 MODIFY zhuming VARCHAR(999)
-- 删除列
ALTER TABLE user1 DROP nicke_name
-- 查看表结构，可以查看表的所有列
DESC user1
-- 修改表名
RENAME TABLE user1 to userOne
-- 修改表的字符集为utf8
ALTER TABLE userOne CHARACTER SET utf8
-- 修改表的列名
ALTER TABLE userOne CHANGE zhuming  xiaozhuming VARCHAR(520) NOT NULL DEFAULT ''

MySql数据类型

1、字符串类型使用char、varchar

CHAR(size),固定长度字符串，最大255字符（size是以字符为单位的）。
VARCHAR(size),0~65535,可变长度字符串，最大65532字节（size是以字符为单位的）【utf8编码最大21844字符 1-3个字节用于记录大小】
如果表的编码是utf8 varchar(size) size=(65535-3)/3=21844
如果表的编码是gbk varchar(size) size=(65535-3)/2=32766

2、日期类型

CRUD操作细节

1、insert

字符和日期型数据应包含在单引号中

-- 插入多条语句
INSERT INTO goods
VALUES
	(
		1,
		'手表',
	26.7)
INSERT INTO goods ( id, goods_name, price ) VALUES ( 3, '华为手机3', 6666.9 ),( 4, '华为手机4', 6666.9 ),
( 5, '华为手机5', 6666.9 )

2、update

-- 将表中某个字段的值全部修改为一个值（没有where条件，慎用）
UPDATE goods SET price=900

-- 将表中某个字段的值增加1000
UPDATE goods SET price=price+1000 where id=4

-- 修改多个列的值

UPDATE goods SET price=price+1000,goods_name='Magic3' where id=4

3、delete

select

CREATE TABLE student (
	id INT NOT NULL DEFAULT 1,
	`name` VARCHAR ( 20 ) NOT NULL DEFAULT '',
	chinese FLOAT NOT NULL DEFAULT 0.0,
	english FLOAT NOT NULL DEFAULT 0.0,
	math FLOAT NOT NULL DEFAULT 0.0 
);

INSERT INTO student ( id, `name`, chinese, english, math )
VALUES
	( 1, '刘备', 89, 78, 90 ),
	( 2, '张飞', 67, 98, 56 ),(
		3,
		'关羽',
		87,
		78,
		77 
		),(
		4,
		'黄忠',
		56,
		85,
		45 
		),(
		5,
		'赵云',
		82,
		84,
		67 
	),
	( 6, '曹操', 88, 89, 94 ),(
		7,
		'孙仲谋',
		75,
		65,
		30 
	);

SELECT
	* 
FROM
	student

SELECT DISTINCT
	english 
FROM
	student

AS可以起别名

-- 统计每个学生的总分
SELECT `name`,( chinese + english + math ) FROM student; -- 所有学生的总分加10分
SELECT `name`,( chinese + english + math + 10 ) AS total_source 
FROM
	student;

-- 查询姓名为赵云的学生成绩
SELECT *FROM student WHERE `name`='赵云'
-- 查询英语成绩大于90分的同学
SELECT * FROM student where english>90
-- 查询总分大于200分的同学
SELECT * FROM student where (chinese+english+math)>200

-- 查询math大于60并且english大于90分的学生的成绩
SELECT * from student WHERE math>60 AND english>80
-- 查询英语成绩大于语文成绩的同学
SELECT * FROM student WHERE english>chinese
-- 查询总分大于200分并且数学成绩小宇=于语文成绩的姓刘的学生
-- 刘%表示  名字以刘开头的就可以
SELECT
	* 
FROM
	student 
WHERE
	( chinese + english + math )> 200 
	AND math > chinese 
	AND `name` LIKE '刘%'
-- 查询英语分数在80-90之间的同学
SELECT *FROM student where english>=80 AND english<=90;
-- between .. and.. 是闭区间
SELECT english FROM student where english BETWEEN 80 AND 90;

select math from student
														

-- 查询数学成绩为90,77,30的同学
SELECT *FROM student WHERE math=90 OR math=77 OR math=30;
SELECT *FROM student WHERE math IN (90,77,30);

-- 对数学成绩排序后输出【升序】
SELECT math FROM student ORDER BY math;
-- 对总分按从高到低的顺序输出
SELECT (chinese+english+math) as Total from student ORDER BY (chinese+english+math) DESC;
-- 对总分按从高到低的顺序输出 别名排序
SELECT (chinese+english+math) as Total from student ORDER BY Total DESC;
-- 对姓刘的学生成绩排序输出（升序）
SELECT `name`,(chinese+english+math) as Total from student WHERE `name`LIKE '刘%' ORDER BY Total

-- 演示mysql的统计函数的使用
-- 统计一个班级共有多少学生
SELECT COUNT(*) FROM student; 
-- 统计数学成绩大于90的学生有多少个？
SELECT COUNT(*) FROM student WHERE math>90;
-- 统计总分大于250的人数有多少？
SELECT COUNT(*) FROM student WHERE (chinese+english+math)>250;
-- count(*)和count(列)的区别
-- 解释 count(*)  返回妈祖条件的记录的行数？
-- count(列)统计满足条件的某列有多少个？排除值为NULL的情况
CREATE TABLE t15 (`name` VARCHAR(20));
INSERT INTO t15 VALUES('jack');
INSERT INTO t15 VALUES('tom');
INSERT INTO t15 VALUES('cat');
INSERT INTO t15 VALUES(NULL);

-- 该值为4
SELECT COUNT(*) FROM t15;
-- 该值为3
SELECT COUNT(`name`) FROM t15;

-- 演示sum函数的使用
-- 统计一个班级学生总成绩
SELECT SUM(math) FROM student;
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(chinese),SUM(english),SUM(math) FROM student;
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(chinese+english+math) FROM student;
-- 统计一个班级语文成绩的平均分
SELECT SUM(chinese)/COUNT(*) FROM student;

-- 演示avg的使用
-- 练习
-- 求一个班级学生平均分？
SELECT AVG(math) FROM student
-- 求一个班级总平均分？
SELECT AVG(chinese+english+math) FROM student;

-- 演示max和min的使用
-- 求班级最高分和最低分（数值范围在统计中特别有用）
SELECT MAX(chinese+english+math), MIN(chinese+english+math) FROM student;

-- #演示group by+having
-- GROUP BY 用于对查询的结果分组统计
-- having字句用于限制分组显示结果。
-- ?如何显示每个部门的平均工资和最高工资
SELECT AVG(sal),MAX(sal),depton FROM emp GROUP BY depton;
-- ?显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal),MIN(sal),depton,job from emp GROUP BY depton,job;
-- ?显示平均工资低于2000 的部门号和它的平均工资
SELECT AVG(sal) AS avg_sal ,depton FROM emp  GROUP BY depton HAVING  avg_sal<2000;

MySql函数——随记2