数据库常用命令.doc

1、存储引擎：Engine=innodb字符集： charset=utf8Character set utf8查看字符集：show character set;show global variables like%char%;show global variables like%collation%;显示字符序：show collation;设置服务器级别：set global character_set_server=gbk;显示服务器级别：show global variables like char%;数据库进入命令：mysql -h hostname -u username -p mysq

2、l -h localhost -u root p帮助：helpHelp create database;mydb 数据库数据库导入命令：mysqldump -h localhost -u root -p mydb e:mysqlmydb.sqlsource e:/song.sql;从外部导入：mysql -h localhost -u root -p mydb2 ENGINE=INNODB;create table Salary(SalaryID int(10) not null,InCome decimal(4,2), OutCome decimal(4,2),Time date, Depa

3、rtmentID int(10) ,primary key(SalaryID),constraint constraint_Salary foreign key(DepartmentID) references Departments(DepartmentID)- engine=innodb;修改表：alter table 表名 属性;创建一个表带默认存储引擎和字符集：create table name (myname varchar(10) engine=innodb charset=utf8;创建相同表结构的表：create table 新表名 like 原来表名;create table

4、 Employees1 like Employees；create table 新表名 select *from 原来表名;变换列：alter table 表名 change 列名 新的列名 新的列的说明; alter table name change myname firstnmae vrachr(20);增加列：alter table 表名 add 列名 列的说明 ;alter table name add lastname varchar(10) not null;修改列：alter table 表名 modify 列名 列的属性（数据类型）alter table name modif

5、y lastname varchar(10);删除列：alter table 表名 drop column 列名：alter table name drop column lastname;删除表：drop table 表名;显示表信息：show tables ;Describe mydb;显示表结构：show create table 表名 ;Show create table G在数据库表中插入信息：insert into 表名 values();Insert into 表名 set a =2016/01/01显示数据库表的内容：select * from 表名;显示系统变量的值：show

6、 variables like tiem_zone;设置时区时间：set tiem_zone=+9：00;修改更新表：update 表名 set 列名=列的内容;update 表名 set a =2016/01/01;数据库表增加列：alter table 表名 add 列名 数据类型 ;创建索引：create index 索引名 on 表名 (列名 )；追加索引：alter table 表名 add index 索引名 (列名)；删除索引：drop index 索引名 on 表名;alter table 表名 drop index 索引名;显示索引：show index from 表名;Sh

7、ow index on 表名；添加主键：alter table 表名 add primary key（列名）;添加唯一索引：alter table 表名 add unique key 或 index 索引名（列名）; 添加多列索引：alter table 表名 add unique key 或 index 索引名（列名，列名，列名）;删除主键索引：;alter table 表名 drop primary key ;创建外键约束：alter table 表名 add constraint constraint_xx 约束的名字 foreign key (那个列是外键) references 主表

8、名（主表的那一列）;删除外键约束：alter table 表名 drop foreign key constraint_xx;创建视图：create view 视图名 as select*from 表名 where status=1;删除视图：drop view 视图名;新建用户：create user xwlocalhost identified by123456;显示用户：select 用户名 from 用户名；查看当前权限：show grants;查看其它用户权限：show grants for testlocahost；查看系统所有权限：show privileges;显示具体权限：s

9、how grant for xw;select user,host from mysql.user;更改用户名：rename user xwlocalhost to xw%;Rename userxw查询权限：select user,host from user;select user();更改用户密码：set password for xw%=password(123);Alter user user_1localhost identified by 111;修改当前密码：set password =password(123)；授权用户：grant all on yggl.Employees

10、 to user_1localhost;grant all on yggl to user_1localhost;grant select,insert on *.* to test2localhost;grant select on yggl.* totest1% identified by123;grant select on yggll.Salary to user_1localhost with grant option;收回权限：revoke select on yggll.Employees from user_1localhost;revoke select on *.* fro

11、m testlocalhost;revoke select on yggl.*fromtest1%;新建用户：mysql create user testlocalhost identified by test123;授权：mysql grant select on *.* to testlocalhost with grant option;新建并授权：mysql grant select on *.* to test2localhost identified by 123456 with grant option;删除用户：drop user user_3localhost;Timesta

12、m p ：可以根据时区自动进行设置显示主机：ipconfig/all主表的约束字段必须有索引：子表+reference +主表实验二2使用SQL命令创建数据库和表 使用命令创建用于企业管理的员工管理数据库 YGGL，默认的字符集设为 utf8.mysql create database YGGL- charset=utf8; 使用命令创建数据库 YGGL 中各表首先将 YGGL 数据库变成当前活动的数据库mysql use YGGL;Database changed 创建部门信息表 Departments，存储引擎设为 innodb.mysql create table Departments

13、(- DepartmentID int(10) not null,- DepartmentName varchar(20),- Note text,- primary key(DepartmentID)- ENGINE=INNODB;Query OK, 0 rows affected (0.39 sec) 创建员工信息表 Employees，存储引擎设为 innodbmysql create table Employees( -EmployeeID int(10) not null,Name varchar(6), Sex Char(2),Birthday date,Education var

14、char(30),WorkYear date,Address varchar(30),PhoneNumbr int(11),DepartmentID int(10) not null,primary key(EmployeeID)ENGINE=INNODB;Query OK, 0 rows affected (0.41 sec) 创建员工薪水情况表 Salary，存储引擎设为 innodbmysql create table Salary(- SalaryID int(10) not null,- InCome decimal(4,2),- OutCome decimal(4,2),- Tim

15、e date,- EmployeeID int(10) not null,- primary key(SalaryID),- constraint constraint_Salary foreign key(EmployeeID) references Employees(EmployeeID)- engine=innodb;Query OK, 0 rows affected (0.42 sec) 创建一个结构与 Employees 表结构相同的空表 Employees1;mysql create table Employees1 like Employees- ;Query OK, 0 ro

16、ws affected (0.20 sec)检查前面创建的表，如果有错误或遗漏的列及主键、外键定义，则用 ALTER TABLE命令修改。mysql alter table Employees add constraint constraint_Employees foreign key(DepartmentID) references Departments(DepartmentID);Query OK, 0 rows affected (0.86 sec)Records: 0 Duplicates: 0 Warnings: 0(4) 使用命令删除数据库和表删除表 Employees;mys

17、ql drop table Employees;Query OK, 0 rows affected (0.14 sec)删除数据库 YGGL;mysql drop database YGGL;Query OK, 3 rows affected (0.44 sec)实验 3 索引和约束实验目的：1掌握索引的使用方法2掌握约束的实现方法。实验要求：了解索引和约束的相关知识。实验内容：1. 创建和删除索引2. 创建和删除约束实验步骤：说明：按实验步骤对数据库 YGGL 中的三个表进行操作，三个表结构如下（具体参看实验2）：Departments (DepartmentID,DepartmentNam

18、e,Note)Employees (EmployeeID,Name,Sex,Birthday,Education,WorkYear,Address,PhoneNumber,DepartmentID)Salary(SalaryID,InCome,OutCome,Time, EmployeeID)要求：完成实验步骤中的SQL。1. 使用CREATE INDEX语句创建索引和约束（1）对YGGL数据库上的Employees表中的Birthday列建立索引in_birth。 mysql create index in_brith on Employees(Birthday); （2）在Employee

19、s表的Name列和Address列上建立复合索引in_name。 mysql create index in_name on Employees(Name,Address); （3）在Departments表的DepartmentName列建立唯一性索引in_depname。mysql create unique index in_depname on Departments(DepartmentName); （4）使用SHOW INDEX语句查看Employees表的索引。mysql show index from Employees; （5）使用SHOW INDEX语句查看Departme

20、nts表的索引。mysql show index from Departments; 2.使用ALTER TABLE语句向表中添加索引和约束（1）向Employees表中的出生日期列添加一个唯一性索引，姓名列和性别列添加一个复合索引。mysql alter table Employees add unique index in_birth(Birthday); mysql alter table Employees add index in_Name_Sex(Name,Sex); （2）删除表Departments的主键。mysql alter table Departments drop p

21、rimary key; （3）将表Departments的DepartmentID列设为主键。mysql alter table Departments add primary key(DepartmentID); （4）删除表salary的现有的外键。mysql alter table Salary drop foreign key constraint_Salary; （5）为表salary添加适合的外键。mysql alter table Salary add constraint constraint_Salary foreign key(EmployeeID) references

22、Employees(EmployeeID); 3.在创建表时创建索引和约束创建与表Departments表相同结构的表Departments1，将DepartmentName列设为主键， DepartmentID列上建立一个索引mysql create table Departments1 select* from Departments; mysql alter table Department1 add index in_DepartmentID (DepartmentID); Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicat

23、es: 0 Warnings: 0 4. 用适合的方法完成下面的操作（1）创建一个表Employees3,只含EmployeeID,Name,Sex和Education列。将Name设为主键，EmployeeID为唯一索引mysql create table Employees3(EmployeeID int(10)not null unique ,Name varchar(6)not null primary key ,Sex char(2),Education varchar(40); Query OK, 0 rows affected (0.26 sec) (2) 创建一个表Salary

24、1,要求所有Salary1表上出现的EmployeeID都要出现在Salary表中，利用完整性约束实现，要求当删除或修改Salary表上的EmployeeID列时，Salary1表中的EmployeeID值也会随之变化。mysql create table Salary1 like Salary; Query OK, 0 rows affected (0.32 sec) mysql alter table Salary1 add constraint constraint_Salary1 foreign key(EmployeeID) references Salary(EmployeeID)

25、 on update cascade; (3) 将表Salary中的数据插入到表Salary1中。ysql insert into salary select*from salary; Query OK, 0 rows affected (0.03 sec) (4) 删除或更新表Salary中的数据时，观察表Salary1中的数据有何变化?当主表里面的数据变化时，附表里面的数据跟着相应的变化实验 4 权限管理实验目的：1掌握数据库用户帐号的建立与管理2掌握数据库用户权限的管理实验要求：1理解数据库安全的重要性2了解 MySQL 的安全机制实验内容：1数据库用户帐号的建立与管理2用户权限的管理实

26、验步骤：说明：按实验步骤对数据库 YGGL 中的三个表进行操作，三个表结构如下（具体参看实验2）：Departments (DepartmentID,DepartmentName,Note)Employees (EmployeeID,Name,Sex,Birthday,Education,WorkYear,Address,PhoneNumber,DepartmentID)Salary(SalaryID,InCome,OutCome,Time, EmployeeID)要求：完成SQL1. 数据库用户帐号的建立与管理（1 ）创建数据库用户user_1和user_2，密码都为1234（服务器为本机服

27、务器，名为localhost） 。mysql create user user_1localhost identified by 123456; Query OK, 0 rows affected (0.02 sec) mysql create user user_2localhost identified by 123456;Query OK, 0 rows affected (0.00 sec)（2 ）将用户user_2 的名称修改为user_3。mysql rename user user_2localhostto user_3localhost;Query OK, 0 rows aff

28、ected (0.00 sec)（3 ）将用户user_3 的密码修改为123456。mysql set password for user_3localhost=password(123456);Query OK, 0 rows affected, 1 warning (0.00 sec)（4 ）删除用户user_3 。mysql drop user user_3localhost;Query OK, 0 rows affected (0.02 sec)（5 ）退出MySQL，再次以user_1用户身份登录MySQL。mysql exit;Byemysql exit;ByeC:Usersas

29、usmysql -u user_1 -pEnter password: *Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 3Server version: 5.7.10-log MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corpo

30、ration and/or itsaffiliates. Other names may be trademarks of their respectiveowners.思考题：1以 user_1 用户身份登录 MySQL 后，可以对服务器端数据库 YGGL 进行查询以及更新操作吗？实际操作试试，并解释原因。不能，因为没有赋予权限2 MySQL 的用户信息存储在 MySQL 哪个数据库的哪个表中？存储在 mysql 数据库里的 user 表里。2用户权限的管理重新以 root 身份登录 MySQL 服务器后运行下面的 SQL 语句：（1 ）授予用户 user_1 对 YGGL 数据库中 Emp

31、loyees 表的所有操作权限。mysql grant all on yggl.Employees to user_1localhost;Query OK, 0 rows affected (0.08 sec)（2 ）授予用户 user_1 对 YGGL 数据库中 Departments 表的查询、插入、修改权限。mysql grant select,insert,update on yggl.Departments to user_1localhost;Query OK, 0 rows affected (0.05 sec)（3 ）授予用户 user_1 对 YGGL 数据库的所有权限。gr

32、ant all on yggl to user_1localhost;Query OK, 0 rows affected (0.06 sec)（4 ）授予用户 user_1 对 YGGL 数据库中 Salary 表上的 SELECT 权限，并允许其将权限授予其它用户。mysql grant select on yggll.Salary to user_1localhost with grant option;Query OK, 0 rows affected (0.03 sec)执行完后可以以 user_1 用户身份登录ySQL，用户可以使用 GRANT 语句将自己在该表的所拥有的权限授予其他

33、用户。mysql grant select on yggl.Salary to user_3localhost;Query OK, 0 rows affected (0.04 sec)（5 ）回收用户 user_1 对 YGGL 数据库中 Employees 表的 SELECT 操作权限。mysql revoke select on yggll.Employees from user_1localhost;Query OK, 0 rows affected (0.03 sec)思考题：1思考表权限、列权限、数据库权限和用户权限的不同之处。列权限：和表中的一个具体列相关。表权限：和一个具体表中的

34、所有数据相关。数据库权限：和一个具体的数据库中的所有表相关。用户权限：和 mysql 所有的数据库相关。实验 5 数据查询实验目的：掌握数据查询 SQL 命令实验要求：掌握数据查询 SELECT 语句的语法格式实验内容：1 SELECT 语句的使用2连接查询和子查询的使用实验步骤：说明：恢复 song.sql 文件中的三个表:Play_list:歌单表Play_fav:歌单收藏表Song_list:歌曲列表要求：仔细观察三个表的结构及各字段含义，写出正确SQL语句。(1) 查询每位艺术家的名字和他的专辑名mysql select artist,album from song_list;(2)

35、按歌单订阅人数降序，显示歌单的信息mysql select play_name,bookedcount from play_list order by bookedcount desc;(3) 按歌单创建时间升序，显示歌单的信息mysql select play_name,createtime from play_list order by createtime;(4）查询每个专辑的歌曲数量mysql select album ,count(song_name) from song_list group by album;（5）查询点播次数最多的歌曲mysql select playcount

36、,song_name from song_list order by playcount desc limit 0,1;（6）查询歌曲数量在2个以上的艺术家及他的歌曲数量mysql select artist ,count(*) from song_list group by artist having count(*)=2;(7)查询订阅人数处于前3名的歌单mysql select play_name,bookedcount from play_list order by bookedcount desc limit 0,3;（8）查询歌曲专辑名中有“Straight”的歌曲信息mysql

37、select album ,song_name from song_list where album like %Straight%;（9）查询每曲歌曲的名字和状态。如状态为1，则显示“已删除” ，若状态为0则显示“正常” ，否则显示“未知”mysql select song_name,status,case status when 1 then 已删除 when 0 then 正常 else 未知 end from song_list;(10) 查询歌曲数量在第 2 位到第 5 位的歌单信息mysql select trackcount, play_name from play_list o

38、rder by trackcount desc limit 1,5;（11 ）查询收藏歌单“每日歌曲推荐”的用户 id 和收藏时间mysql select userid,play_fav.createtime,song_name from song_list ,play_fav where song_list.id=play_fav.userid;（12 ）查询每位用户收藏的歌曲名字及收藏时间mysql select play_name,play_fav.userid,play_fav.createtime from play_fav,play_list where play_id = id;

39、（13 ）查询所有歌单的被用户收藏的情况mysql select play_name,play_id,play_fav.createtime,play_fav.status, case play_fav.status when 0 then 正常 when 1 then 已删除 else 未知 end from play_list,play_fav;+-+-+-+-+-+| play_name | play_id | createtime | status | case play_fav.status when 0 then 正常 when 1 then 已删除 else 未知 end |（1

40、4 ）查询所有的歌单名和歌曲名mysql select play_name,song_name from song_list,play_list;实验 5 数据查询（2）实验目的：掌握数据查询 SQL 命令实验要求：掌握数据查询 SELECT 语句的语法格式实验内容：SELECT 语句的使用实验步骤：说明：按实验步骤对数据库 YGGL 中的三个表进行数据查询，三个表结构如下（具体参看实验 2）：Departments (DepartmentID,DepartmentName,Note) Employees(EmployeeID,Name,Sex,Birthday,Education,WorkY

41、ear,Address,PhoneNumber,DepartmentID)Salary(SalaryID,InCome,OutCome,Time, EmployeeID)要求：写出正确SQL语句。(1)查询每个雇员的所有信息mysql select*from employees;(2) 查询每个部门的部门号和部门名mysql select DepartmentID,DepartmentName from Departments;(3) 查询每个雇员的学历，消除重复行。mysql select distinct Education , Name from employees;(4）查询员工号为0

42、0001的员工的地址和电话mysql select Address , PhoneNumber,EmployeeID from employees where EmployeeID=000001;(5)查询月收入高于2000元员工号。mysql select EmployeeID,InCome from salary where InCome = 2000;(6)查询1970年以后出生的员工姓名和地址。mysql select Name ,Address,Birthday from Employees where Birthday 1970;(7)查询女员工的地址和电话，并使用AS子句将结果中

43、各列的标题分别指定为地址、电话。mysql select Address as 地址 ,PhoneNumber as 电话 from Employees where Sex =0;(8)查询男员工的姓名和出生日期，并使用AS子句将结果中各列的标题分别指定为姓名、出生日期。mysql select Name as 姓名 ,Birthday 出生日期 from Employees where Sex=1;（9）查询员工的姓名和性别，要求Sex值为1时显示“男” ，值为0时显示为“女” 。mysql select Name,Sex ,case Sex when 1 then 男 when 0 the

44、n 女 end from Employees;（10）查询员工2012年12月的薪水号和收入水平，收入为2000元以下显示为低收入，2000-3000元显示为中等收入，3000元以上显示为高收入。mysql select SalaryID ,InCome ,case when InCome3000 then 高收入 end as 收入水平 from Salary where time between 2012.12.01 and 2012.12.31;（11）查询所有姓“王”员工的部门号及姓名。mysql select DepartmentID,Name from Employees wher

45、e name like 王%;（12）找出其地址中不含有“中山”两字的员工的号码、部门号和地址。mysql select PhoneNumber,EmployeeID,DepartmentID,Address from Employees where Address not like%中山%;（13）找出员工号码中倒数第二个数字为0的员工的姓名、地址和学历。mysql select Name,Address, Education from Employees where EmployeeID like%0_;（14）查询所有2012年11月收入在2000到3000之间的员工号码。mysql s

46、elect EmployeeID from salary where InCome between 2000 and 3000 and time 2012;（15）查询所有工作时间不在3到6年之间的所有员工的员工号，员工名和工作时间。mysql select Name,EmployeeID ,WorkYear from Employees where year(now() -year(WorkYear) not between 3 and 6;（16）查询男员工和女员工的人数。mysql select case sex when 1 then 男 when 0 then 女 end as 性别

47、,count(employeeid) as 人数 from employees group by sex;（17）按部门列出该部门的员工人数。mysql select DepartmentName,count(DepartmentName) from Employees, Departments where Employees.DepartmentID=Departments.DepartmentID group by DepartmentName;（18）按学历列出该学历的员工人数。mysql select Education ,count(EmployeeID) from employees group by Education;（19）查询员工数超过2人的部门名称和员工数量。mysql select DepartmentName ,coun