主要是mysql 的命令的实践
安装mysql 请移步 django 的那一篇
1.登陆
链接数据库
（mysql -h hostname -u user_name -p ）
mysql -u root -p
mysql -u fang -p （此处不用带 local fang@localhost）
mysql -u root -p --show-warnings; （会显示具体警报信息）
查看有哪些数据库
show databases;
退出
exit（回车） 或则直接 ctol + c
Q1
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.（需要将初始密码改掉）
运行命令
SET PASSWORD = PASSWORD('123456');
2.创建用户
create user '用户名'@'主机名' identified by '密码';
create user 'fang'@'localhost' identified by '123456';
查看当前登陆的用户
select current_user();
3.分配权限
创建数据库
create database movietalk；
创建权限
grant all privileges on movicetalk.* to fang@localhost;
使权限生效
flush privileges;
image.png
image.png
4.显示用户信息```
所有的用户全部存在 mysql.user 这张表里
select user from mysql.user (直接回车，下一行就看到箭头，；回车)
//查看表中所有字段
desc mysql.user;
//查看表中某些字段
select user, host from mysql.user;
// 查看用户是否拥有某个权限
select user,select_priv from mysql.user;（select_priv，是否有选择表的权限）
// 查看用户在某个特定的数据库（db）上的权限
select user, db, select_priv from mysql.db;
// 查看某个用户的权限设置情况
show grants for fang@localhost;
image.png
4.吊销用户权限
// 取消 某个用户 对应的操作权限
（此处为取消fang用户对于movicetalk 数据库所有的表的 更新与 删除的权限）
revoke update, delete on movicetalk.* from fang@localhost;
//删除完之后查看下刚才的权限
select user, db, update_priv, delete_priv from mysql.db;
image.png
5.重设密码与删除用户
// 重设密码
set password for fang@localhost = password('hello');
// 删除用户
drop user fang@localhost；
6.创建 使用 删除 数据库
// 创建数据库
create database movice;
create database if not exists movice;(如果不存在，就创建，避免报错)
show databases;
// 切换到数据库
use movice; (显示 Database changed )
// 删除数据库
drop database movice;
7.创建数据表
create database movice; //创建数据库
use movice;// 切换到当前数据库
show tables;// 查看表
// 创建表
create table film(
-> film_name varchar(255),
-> film_date date
-> );
// 查看表里面的具体的列
describe film;
image.png
8. 添加数据栏
·```
// 添加数据栏
alter table film add id INT(10) first;
alter table film add film_content TEXT after film_name;
//设置主键
alter table film add PRIMARY KEY (id);
9. 修改数据栏与表
// 修改列的名字
alter table film change id film_id INT(10);
//修改表的名字
alter table film rename to movice;
// 删除一列
alter table movice drop film_content;
// 删除表
drop table movice;
image.png
image.png
image.png
image.png
10. 重新创建数据库与数据表
https://ninghao.net/video/1760
//删除数据库
drop database movicetalk;
image.png
//创建表的过程
create database movicetalk chaeset=uft-8;
use movicetalk;
create table people(
-> people_id INT(10) unsigned not null auto_increment,
-> people_name VARCHAR(100),
-> people_brich DATE,
-> people_location VARCHAR(100),
-> primary key(people_id)
-> ) default charset=utf8
-> ;
//查看下
describe people;
image.png
10.插入数据
insert into people values (null,'张艺谋','1950-08-09','中国');
insert into people(people_name,people_location) values ('陈凯歌','中国');
image.png
image.png
11.选择数据
select * from people;
select people_name, people_brich from people;
select * from people where people_name = '张艺谋';
select * from people order by people_brich asc;
select * from people order by people_brich desc;
image.png
image.png
12.跟新与删除数据
update people set people_brich = '1970-01-01' where people_id = 2;
delete from people where people_id =1;
image.png
13.限制结果 与 数量
select * from people where people_location = '中国' limit 3;
select * from people where people_location = '中国' limit 3 offset 1;
select * from people where people_location = '中国' limit 1,3;
image.png
14.操作符
select * from people where people_brich > '1965-0101' and people_brich < '1975-12-12';
select * from people where people_location in ('中国','台湾');
select * from people where people_location not in ('中国');
select * from people where people_name like ('张%');
image.png
part two 关系
15.为创建关系做准备
// 命令导入数据库
mysql -u root -p movietalk < movietalk.sql;
select user_name, review_content from user, review where user.user_id = review.review_id;
select user_name, review_content from user inner join review on review.review_id = user.user_id;
select user_name, review_content from user inner join review on review.review_id = user.user_id where user.user_id =1;
image.png
16.左关联
select user_name, review_content from user left join review on review.user_id = user.user_id;
image.png
17.统计 平均 分组 函数
select count(review_id) from review;
select film_id ,count(review_id) from review group by film_id;
select film_id, avg(review_id) from review group by film_id;
select review.film_id, film.film_name, avg(review_rate) from review, film where review.film_id = film.film_id group by review.film_id;
image.png
18.三个表的关联
mysql> select film_name, people_name, job from film, people, film_people where film_people.film_id = film.film_id and film_people.people_id = people.people_id;
select film_name, people_name, job from film, people, film_people where film_people.film_id = film.film_id and film_people.people_id = people.people_id and film_name = '无间行者';
select film_name, people_name, job from film, people, film_people where film_people.film_id = film.film_id and film_people.people_id = people.people_id and people_name like '马丁%';
// 统计导演的票房 as 别名
select sum(film_box) as total_box, people_name from film, people, film_people where film_people.film_id = film.film_id and film_people.people_id = people.people_id and job = '导演' group by people_name order by total_box desc;
image.png
image.png
网友评论