数据库常用命令.doc

存储引擎：Engine=innodb字符集： charset=utf8 Character 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 mysql -h localhost -u root p帮助：help Help 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, DepartmentID 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 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 modify 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 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 表名; Show 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 主表名（主表的那一列）;删除外键约束：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;显示具体权限：show 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 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 *.* from 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;Timestam 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( - 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 varchar(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), - Time 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 rows 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;mysql 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,DepartmentName,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）在Employees表的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语句查看Departments表的索引。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 primary 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 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 Duplicates: 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) 创建一个表Salary1,要求所有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) 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用户权限的管理实验步骤：说明：按实验步骤对数据库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（服务器为本机服务器，名为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 affected (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:Usersasusmysql -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 Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.思考题：1以user_1用户身份登录MySQL后，可以对服务器端数据库YGGL进行查询以及更新操作吗？实际操作试试，并解释原因。不能，因为没有赋予权限2MySQL的用户信息存储在MySQL哪个数据库的哪个表中？存储在mysql数据库里的user表里。2用户权限的管理重新以root身份登录MySQL服务器后运行下面的SQL语句：（1）授予用户user_1对YGGL数据库中Employees表的所有操作权限。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数据库的所有权限。grant 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语句将自己在该表的所拥有的权限授予其他用户。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思考表权限、列权限、数据库权限和用户权限的不同之处。列权限：和表中的一个具体列相关。表权限：和一个具体表中的所有数据相关。数据库权限：和一个具体的数据库中的所有表相关。用户权限：和mysql所有的数据库相关。实验5 数据查询实验目的：掌握数据查询SQL命令实验要求：掌握数据查询SELECT语句的语法格式实验内容：1SELECT语句的使用2连接查询和子查询的使用实验步骤：说明：恢复song.sql文件中的三个表:Play_list:歌单表Play_fav:歌单收藏表Song_list:歌曲列表要求：仔细观察三个表的结构及各字段含义，写出正确SQL语句。(1) 查询每位艺术家的名字和他的专辑名mysql select artist,album from song_list; (2) 按歌单订阅人数降序，显示歌单的信息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,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 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 order 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;（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 |（14）查询所有的歌单名和歌曲名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,WorkYear,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）查询员工号为00001的员工的地址和电话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子句将结果中各列的标题分别指定为地址、电话。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 then 女 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 where 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 select 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 性别,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 ,count(EmployeeID) from Employees ,Departments where Employees.DepartmentID=Departments.DepartmentID group by DepartmentName having count(DepartmentName)=2;（21）将员工信息按出生日期从小到大排列。mysql select * from employees order by Birthday desc;（22）查询Employees表中前5位员工信息。mysql select * from employees order by EmployeeID limit 0,5;(23) 查询Employees表中第5到第10位员工信息。mysql select * from employees order by EmployeeID limit 4,10;（24）查询在财务部工作的雇员的情况。mysql select* from employees ,departments where departments.departmentid=employees.departmentid and departmentname=财务部;mysql select * from employees where DepartmentID = (select DepartmentID from departments where DepartmentName=财务部);（25）查找所有收入在2500以下的员工情况。mysql select*from Employees where EmployeeI