Sql数据库学习笔记

-- 数据库用户
-- 查看数据库的用户
SELECT user,host from mysql.user
-- 修改root的密码
ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password BY 'Eleven11.';
-- 设置root远程登录
update mysql.user set Host='%' where User='root'; 
-- 新建用户test用户 并指定任何ip登录
CREATE USER test@'%' IDENTIFIED with mysql_native_password by 'Eleven11!'
-- 给test用户设置权限 拥有test数据库的所有权限
GRANT ALL ON test.* to test@'%'
-- 删除test用户
drop user test@'%'


-- 数据库的创建
-- 查询数据库支持的编码
show COLLATION WHERE collation like '%utf8mb4%'
-- 
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci         | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs         | utf8mb4 | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin           | utf8mb4 | 309 |         | Yes      |       1 | NO PAD        |
| utf8mb4_bg_0900_ai_ci      | utf8mb4 | 318 |         | Yes      |       0 | NO PAD        |
| utf8mb4_bg_0900_as_cs      | utf8mb4 | 319 |         | Yes      |       0 | NO PAD        |
.....
-- 创建test的数据库并设置编码
CREATE DATABASE test CHARACTER set utf8mb4 COLLATE utf8mb4_general_ci;
-- 创建一个学生信息表
CREATE TABLE test.student (
    id int not null PRIMARY KEY AUTO_INCREMENT COMMENT '学生id',
    name VARCHAR(6) not null COMMENT '姓名',
    sexs VARCHAR(3)  COMMENT '性别',
    age int COMMENT '年龄',
    mail VARCHAR(255) COMMENT '邮箱'
) ENGINE=INNODB CHARACTER set utf8mb4 COMMENT '学生信息表'

-- 查询表结构 
desc test.student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(6)   | NO   |     | NULL    |                |
| sexs  | varchar(3)   | YES  |     | NULL    |                |
| age   | int          | YES  |     | NULL    |                |
| mail  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

-- 插入表的数据
-- 插入多行数据
INSERT into test.student VALUES
(1,'张山','男',18,'123@123.com'),
(2,'王五','男',17,'126@aaa.com'),
(3,'奥速','女',17,'bsc@wqe.com')

-- 插入单行数据
INSERT into test.student VALUES (
    4,'阿松','女',16,'das@gad.cn'
)

--查询数据 
SELECT * FROM test.student
+----+--------+------+------+-------------+
| id | name   | sexs | age  | mail        |
+----+--------+------+------+-------------+
|  1 | 张山   | 男   |   18 | 123@123.com |
|  2 | 王五   | 男   |   17 | 126@aaa.com |
|  3 | 奥速   | 女   |   17 | bsc@wqe.com |
|  4 | 阿松   | 女   |   16 | das@gad.cn  |
+----+--------+------+------+-------------+

-- 统计中国每个省的总人口，找出大于500w的，并按照总人口的从大到小排序
SELECT District,sum(Population) 
FROM world.city 
WHERE CountryCode='chn' 
GROUP BY District 
HAVING sum(Population) > 5000000
ORDER BY sum(Population) DESC;

+--------------+-----------------+
| District     | sum(Population) |
+--------------+-----------------+
| Liaoning     |        15079174 |
| Shandong     |        12114416 |
| Heilongjiang |        11628057 |
| Jiangsu      |         9719860 |
| Shanghai     |         9696300 |
| Guangdong    |         9510263 |
| Hubei        |         8547585 |
| Jilin        |         7826824 |
| Peking       |         7569168 |
| Sichuan      |         7456867 |
| Henan        |         6899010 |
| Hebei        |         6458553 |
| Chongqing    |         6351600 |
| Zhejiang     |         5807384 |
| Hunan        |         5439275 |
| Tianjin      |         5286800 |
| Anhui        |         5141136 |
+--------------+-----------------+
17 rows in set (0.00 sec)



-- ===================================连表查询建表

CREATE TABLE student (
	sno int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
	sname VARCHAR(255) NOT NULL COMMENT '学生姓名',
	sage int COMMENT '学生年龄',
	ssex  VARCHAR(2) COMMENT '学生性别'
)ENGINE=INNODB CHARACTER SET utf8mb4 COMMENT '学生表'

CREATE TABLE teacher (
	tno int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '教师编号',
	tname VARCHAR(10) NOT NULL COMMENT '教师名字'
) ENGINE=INNODB CHARACTER SET utf8mb4 COMMENT '教师表'

CREATE TABLE course (
	cno  int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '课程编号',
	cname VARCHAR(255) NOT NULL COMMENT '课程名字',
	tno int COMMENT '教师编号'
) ENGINE=INNODB CHARACTER SET utf8mb4 COMMENT '课程表'

CREATE TABLE score (
	sno int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
	cno int COMMENT '课程编号',
	score int COMMENT '成绩'
) ENGINE=INNODB CHARACTER SET utf8mb4 COMMENT '成绩表'



-- ============================================
-- 查询世界上人口小于100人的城市名,国家名,国土面积
SELECT city.`Name` , country.`Name` ,country.SurfaceArea FROM world.country 
JOIN world.city
ON country.`Code` = city.CountryCode
WHERE world.city.Population < 100

+-----------+----------+-------------+
| Name      | Name     | SurfaceArea |
+-----------+----------+-------------+
| Adamstown | Pitcairn |       49.00 |
+-----------+----------+-------------+
1 row in set (0.00 sec)

-- 查询城市shenyang,城市人口，所在国家名(name) 及国土面积(SurfaceArea)
SELECT world.city.name as '城市名称' ,world.city.Population as '城市人口' ,world.country.name as '国家名称',world.country.SurfaceArea as '国土面积'
FROM world.city
JOIN world.country
ON country.Code = city.CountryCode
WHERE world.city.Name='shenyang'

+--------------+--------------+--------------+--------------+
| 城市名称     | 城市人口     | 国家名称     | 国土面积     |
+--------------+--------------+--------------+--------------+
| Shenyang     |      4265200 | China        |   9572900.00 |
+--------------+--------------+--------------+--------------+
1 row in set (0.01 sec)