1 配置环境变量:右击此电脑点击属性,点击高级系统设置,点击环境变量,在系统变量中找到path,然后点击
编辑,点击新建输入地址如:“C:\Program Files\MySQL\MySQL Server 5.7\bin”
- 在 C:\WINDOWS\System32 中找到cmd.exe 以管理员身份运行 cmd
在cmd中 输入 net start mysql57
3.以用户身份 cmd 输入 ‘mysql -u root -p’
MySql 基本命令
1.启动服务:
以管理员身份运行cmd
格式:net start 服务名称
示例:net start mysql57
2.停止服务:
以管理员身份运行cmd
格式:net stop 服务名称
示例:net stop mysql57
3 连接数据库
格式:mysql -u 用户名 -p
示例:mysql -u root -p
4.退出登陆(断开连接)
quit或exit
5.查看版本(连接后可以执行)
示例:select version();
6,显示当前时间(连接后可以执行)
示例:select now();
7,远程连接
格式:mysql -h ip地址 -u 用户名 -p
输入对方密码:
二,1.创建数据库
格式:create database 数据库名 charset=utf8;
示例:create database kege charset=utf8;
2.删除数据库
格式:drop database 数据库名;
示例:drop database sunck;
3.切换数据库
格式:use 数据库名;
示例: use sunck;
4.查看当前选择的数据库
select database();
5.查看数据库列表
show databases;
三,表操作
1,查看当前数据库中所有表
show tables;
2,创建表
格式: create table 表名(列及类型)
说明:auto_increment 表明自增长 primary key 主键 not null 不为空
示例:create table stu(id int auto_increment primary key,
name varchar(20) not null,
age int not null,
gender bit default 1,
address varchar(20),
isDelete bit default 0 );
3.删除表
格式: drop table 表名;
示例: drop table student;
4.查看表结构
格式: desc 表名;
示例: desc student;
5.查看建表语句
格式:show create table 表名;
示例 :show create table student;
6, 查看备注
SHOW FULL COLUMNS FROM student
7.重命名表名
格式: rename table 原表名 to 新表名;
示例: rename table car to newcar;
格式: alter table 原表名 rename 新表名;
8.添加字段
格式:alter table 表名 add 字段名 数据类型 [属性];
示例:alter table student add gradeId int unsigned comment ‘年级编号’;
9.修改字段
格式:alter table 表名 change 原字段名 新字段名 数据类型 [属性];
示例:alter table student change gradeId ID int not null;
10.删除字段
格式:alter table 表名 drop 字段名;
示例:alter table student drop ID;
11.添加主键约束
格式:alter table 表名 add constraint 主键名 primary key 表名(主键名);
示例:alter table demo1 add constraint pk_name primary key demo1(name);
12.添加外键约束
格式:alter table 表名 add constraint 外键名 foreign key (外键字段) references 关联表名 (关联字段);
示例:alter table student add constraint fk_k foreign key (studentname) references demo1(name);
13.取消主键
示例: ALTER TABLE result
DROP PRIMARY KEY;
示例:alter table class modify id int(11),drop primary key;
14.设置自增列的初始值和步长(设置数据库的自增)
SET @@auto_increment_increment=3;
SET @@auto_increment_offset=4;
15.查看自增列的初始值和步长
SHOW VARIABLES LIKE ‘auto_inc%’;
16.在创建数据库的时候创建外键:
格式:FOREIGN KEY (外键名) REFERENCES 关联表(关联主键字段),
示例: CREATE TABLE hos_house
(hMid
INT PRIMARY KEY NOT NULL AUTO_INCREMENT,uid
INT NOT NULL,sid
INT NOT NULL,hTid
INT NOT NULL,price
DECIMAL NOT NULL DEFAULT 0,topic
VARCHAR(20) NOT NULL,contens
VARCHAR(20) NOT NULL,hTime
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,copy
VARCHAR(20),
FOREIGN KEY (uid) REFERENCES sys_user
(uid),
FOREIGN KEY (sid) REFERENCES hos_street(sid),
FOREIGN KEY (hTid) REFERENCES hos_type(hTid)
)
16.设置当前列的默认值为当前时间
*将该列的属性设为 TIMESTAMP 将默认值设为 CURRENT_TIMESTAMP
四.高级查询
1.插入多行数据
格式:insert into 新表 (字段列表) values((值列表1),(值列表2),(值列表n));
示例:INSERT INTO student
VALUES(‘李斯’,20),(‘赵四’,36),(‘马六’,56)
2.将结果插入新表
格式:create table 新表 (select 字段1,字段2,… from 原表);
示例:CREATE TABLE stu (SELECT name,ane FROM student)
3.更新数据记录
格式:update 表名 set 字段1=值1,字段2=值2,…字段n=值n [where 条件];
示例:UPDATE student SET NAME =‘九九’ ,ane=12 WHERE NAME=‘ac’
4.删除数据
格式:delete from 表名 [where 条件];
示例:DELETE FROM student WHERE NAME=‘张三’;
5.删除表中所有记录
格式:Truncate table 表名
示例:Truncate table student;
6.limit 子句
格式:select <字段名列表> from <表名或视图>
[where <查询条件>]
[group by <分组的字段名>]
[order by <排序的列名>[ASC或DESC]]
[LIMIT [位置偏移量,]行数]] 解释:从0开始显示6条数据
示例:SELECT NAME,ane FROM student LIMIT 0,6;
7.简单子查询
格式:select …from 表1 where 字段1 比较运算符 (子查询);
示例:SELECT studentno
,studentname
,sex
, borndate
,address
FROM student
where bornDate
>(SELECT bornDate
FROM student
WHERE studentName
=‘李斯文’)
8.in 子查询
格式:select …from 表1 where 字段1 in(子查询);
示例:SELECT studentName
FROM student
WHERE studentNo
IN( SELECT studentNo
FROM result
WHERE subjectNo
= ( SELECT subjectNo
FROM subject
WHERE subjectName
=‘Logic Java’)AND studentResult
= 60 );
9.not in 子查询
格式:select ...from 表1 where 字段1 not in(子查询);
示例:/*查询得到未参加Logic Java课程最近一次考试在读学生姓名的功能*/
SELECT `studentNo`, `studentName` FROM `student` WHERE `studentNo`
NOT IN( SELECT `studentNo` FROM `result`
WHERE `subjectNo` = (
#获得参加Logic Java课程最近一次考试的学生学号
SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java'
) AND `examDate` = (
#获得Logic Java课程最近一次的考试日期
SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo` = (
#获得Logic Java课程的课程编号
SELECT `subjectNo` FROM `subject`
WHERE `subjectName`='Logic Java')
)
)
AND `gradeID` = (
SELECT `gradeID` FROM `subject`
WHERE `subjectName` = 'Logic Java'
);
10.exists 子查询
格式:select ...from 表1 where exists(子查询);
示例: /*检查Logic Java课程最近一次考试。如果有成绩达到80分以上者,则显示分数排在前5名学员的学号和分数*/
SELECT `studentNo` AS 学号,`studentResult` 成绩 FROM `result`
WHERE EXISTS (
#查询Logic Java最后一次考试成绩大于80的记录
SELECT * FROM result
WHERE subjectNo
= (
SELECT subjectNo
FROM subject
WHERE subjectName
= ‘Logic Java’
) AND examDate
= (
SELECT MAX(examDate
) FROM result
WHERE subjectNo
= (
SELECT subjectNo
FROM subject
WHERE subjectName
= ‘Logic Java’)
) AND studentResult
> 80)
AND subjectNo
= ( SELECT subjectNo
FROM subject
WHERE subjectName
= ‘Logic Java’)
ORDER BY studentResult
DESC LIMIT 5; #按成绩降序排序,显示前5名
10. not exists 子查询
格式:select …from 表1 where not exists(子查询);
示例:/如果没有考试通过的学员,则平均分加5分/
SELECT AVG(studentresult)+10 AS 平均分 FROM result
WHERE NOT EXISTS (
#查询Logic Java最后一次考试成绩小于60的记录
SELECT * FROM result
WHERE subjectNo
= (
SELECT subjectNo
FROM subject
WHERE subjectName
= ‘Logic Java’
) AND examDate
= (
SELECT MAX(examDate
) FROM result
WHERE subjectNo
= (
SELECT subjectNo
FROM subject
WHERE subjectName
= ‘Logic Java’)
) AND studentResult
> 60)
AND subjectNo
= ( SELECT subjectNo
FROM subject
WHERE subjectName
= ‘Logic Java’)
AND examDate
= (
SELECT MAX(examDate
) FROM result
WHERE subjectNo
= (
SELECT subjectNo
FROM subject
WHERE subjectName
= ‘Logic Java’) );
11.创建临时表
CREATE TEMPORARY TABLE 表名 (查询语句);
示例:CREATE TEMPORARY TABLE house
(
SELECT * FROM hos_house
LIMIT 2,5)
12.
五 事务,视图,索引,备份,和恢复
1.开始事务:begin 或start transaction;
2.提交事务:commit;
3. 回滚(撤销)事务: rollback;
---------------------------------------------------------------------示例---------------------------------------------------------------------------------
mysql> use mybank;
Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> UPDATE bank
set currentmoney
=currentmoney
-500
-> where customerName
=‘张三’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE bank
set currentmoney
=currentmoney
+500
-> where customerName
=‘李四’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from bank;
±-------------±-------------+
| customerName | currentmoney |
±-------------±-------------+
| 张三 | 500.00 |
| 李四 | 501.00 |
±-------------±-------------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE bank
set currentmoney
=currentmoney
-500
-> where customerName
=‘张三’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from mybank;
ERROR 1146 (42S02): Table ‘mybank.mybank’ doesn’t exist
mysql> select * from bank;
±-------------±-------------+
| customerName | currentmoney |
±-------------±-------------+
| 张三 | 0.00 |
| 李四 | 501.00 |
±-------------±-------------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
±-------------±-------------+
| customerName | currentmoney |
±-------------±-------------+
| 张三 | 0.00 |
| 李四 | 501.00 |
±-------------±-------------+
2 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE bank
set currentmoney
=currentmoney
-500
-> where customerName
=‘张三’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from bank;
±-------------±-------------+
| customerName | currentmoney |
±-------------±-------------+
| 张三 | -500.00 |
| 李四 | 501.00 |
±-------------±-------------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from bank;
±-------------±-------------+
| customerName | currentmoney |
±-------------±-------------+
| 张三 | 0.00 |
| 李四 | 501.00 |
±-------------±-------------+
2 rows in set (0.00 sec)
2.设置自动提交关闭或开启
格式:set autocommit =0|1;
mysql> set autocommit=0
-> ;
Query OK, 0 rows affected
mysql> update bank
set currentMoney
=currentMoney
-500
-> where customerName
=‘李四’;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update bank
set currentMoney
=currentMoney
+500
-> where customerName
=‘张三’;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected
mysql> show tables;
±----------------+
| Tables_in_mybank |
±----------------+
| bank |
±----------------+
1 row in set
mysql> select * from bank;
±-------------±-------------+
| customerName | currentmoney |
±-------------±-------------+
| 张三 | 1500 |
| 李四 | -499 |
±-------------±-------------+
2 rows in set
mysql> update bank
set currentMoney
=currentMoney
+500
-> where customerName
=‘李四’;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from bank;
±-------------±-------------+
| customerName | currentmoney |
±-------------±-------------+
| 张三 | 1500 |
| 李四 | 1 |
±-------------±-------------+
2 rows in set
mysql> rollback;
Query OK, 0 rows affected
mysql> select * from bank;
±-------------±-------------+
| customerName | currentmoney |
±-------------±-------------+
| 张三 | 1500 |
| 李四 | -499 |
±-------------±-------------+
2 rows in set
mysql> set autocommit=1;
Query OK, 0 rows affected
五,创建和使用视图
1.使用sql语句创建视图
格式:create view 视图名 as <select 语句>;
示例:CREATE VIEW view_stu1
AS SELECT *FROM student WHERE sex =‘女’ ;
2.使用sql语句删除视图
格式:drop view [if exists] 视图名
示例:DROP VIEW IF EXISTS view_stu1;
3.使用sql语句查看视图数据
格式:select 字段1, 字段2,… from view_name
示例:SELECT studentName FROM view_stu WHERE sex=‘女’;
六,索引
1.创建索引
格式: create [UNIQUE|FULLTEXT|SPATIAL] INDE index_name
on table_name (column_name[lenght]);
2.删除索引
格式: drop index index_name on table_name;
3.查看索引
格式:show index from table_name;
示例:SHOW INDEX FROM student;
七,数据库的备份和恢复
1.使用mysqldump 命令备份数据库
格式:mysqldump -u username -h host -p password dbname [tbname1,tbname2,… ]>filename.sql;
示例:mysqldump -u root -p myschool student>C:\Users\赵\Desktop\myschool.sql
2.使用mysql命令恢复数据库(需要创建新的数据库)
格式:mysql -u username -p [dbname]<filename.sql
示例:mysql -u root -p myschool <C:\Users\赵\Desktop\myschool.sql
格式:source filename;
示例:source C:\Users\赵\Desktop\myschool.sql;
八1,表数据导出到文本文件
格式:select columnlist from tablename
[where contion]
into outfile ‘filename’ [option];
示例: SELECT studentName
,studentNo
FROM student
INTO OUTFILE ‘stu.txt’;
2. 文本文件导入到数据表
LOAD DATA INFILE filename INTO TABLE tablename [OPTION];
示例:LOAD DATA INFILE ‘D:\stu.txt’ INTO TABLE stu
;
创建用户
3. 格式: CREAET USER username
@host
[IDENTIF BY [PASSWORD]‘password’];
示例:CREATE USER testc
@loalhost
IDENTIFIED BY ‘12345’ ;
4.创建用户并授权
格式:GRANT priv_type ON databasesename.tablename TO username
@host
[IDENTIFIED BY [PASSWORD]oassword
]
[WHERE GRANT OPTION];
示例: GRANT SELECT ON myschool.student TO ‘lo’@‘localhost’ IDENTIFIED BY ‘1213’;
5.使用mysqladmin命令修改root账户密码
格式:mysqladmin -u username -p password “newpassword”;
示例:mysqladmin -u root -p password 120
6.使用set命令修改用户密码:
格式:SET PASSWORD [FOR username
@host
]=PASSWORD[“nrewpassword”];
示例:修改当前登陆密码
SET PASSWORD =PASSWORD(“000”)
示例:修改其他用户密码
SET PASSWORD FOR teacher
@localhost
=PASSWORD(“1234”);
7.创建检查约束
格式:ALTER TABLE 表名 add CONSTRAINT 检查名 CHECK(检查约束字段);
示例:ALTER TABLE emp ADD CONSTRAINT ck_emp_sex CHECK
(sex =‘男’ OR sex=‘女’);
8.删除用户:
格式:DROP USER username1
@host
[,username2
@host
……];
示例:DROP USER `student`@`localhost`;
九 ,存储引擎
1.查看系统支持的引擎类型
show engines;
2.查看当前的默认储存引擎
show variables like ‘storage_enginee%’;
3.修改默认储存引擎 ,修改my.ini文件
default-storage-engine=InnDB
把 default-storage-engine的值为MyISAM
4.指定表的储存引擎
create table 表名(#省略代码)ENGINE=储存引擎
十 ,系统帮助
help 查询内容
1.主键语
①创建时:
create table sc (studentno int, courseid int, score int, primary key (studentno) );
②修改时:
ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(列名);
前提是原先没有设置主键。
2.外键语法
①创建时:
create table sc (studentno int, courseid int, score int, foreign key (courseid) );
②修改时:
ALTER TABLE news_info[子表名] ADD CONSTRAINT FK_news_info_news_type[约束名] FOREIGN KEY (info_id)[子表列] REFERENCES news_type[主表名] (id)[主表列] ;
3.使用组合主键
如果一列不能唯一区分一个表里的记录时,可以考虑多个列组合起来达到区分表记录的唯一性,形式
①创建时:
create table sc (studentno int, courseid int, score int, primary key (studentno,courseid) );
②修改时:
alter table tb_name add primary key (字段1,字段2,字段3);
前提是原来表中没有设置主键,若原先已有主键则会报错。
2.模糊查询
语法:
SELECT 字段 FROM 表 WHERE 某字段 Like 条件
SELECT * FROM [user] WHERE u_name LIKE ‘%三%’
1、LIKE’Mc%’ 将搜索以字母 Mc 开头的所有字符串(如 McBadden)。
2、LIKE’%inger’ 将搜索以字母 inger 结尾的所有字符串(如 Ringer、Stringer)。
3、LIKE’%en%’ 将搜索在任何位置包含字母 en 的所有字符串(如 Bennet、Green、McBadden)。
4、LIKE’_heryl’ 将搜索以字母 heryl 结尾的所有六个字母的名称(如 Cheryl、Sheryl)。
5、LIKE’[CK]ars[eo]n’ 将搜索下列字符串:Carsen、Karsen、Carson 和 Karson(如 Carson)。
6、LIKE’[M-Z]inger’ 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称(如 Ringer)。
7、LIKE’M[^c]%’ 将搜索以字母 M 开头,并且第二个字母不是 c 的所有名称(如MacFeather)。
1、’% ':表示任意0个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。
比如 SELECT * FROM [user] WHERE u_name LIKE ‘%三%’
将会把u_name为“张三”,“张猫三”、“三脚猫”,“唐三藏”等等有“三”的记录全找出来。
另外,如果需要找出u_name中既有“三”又有“猫”的记录,请使用and条件
SELECT * FROM [user] WHERE u_name LIKE ‘%三%’ AND u_name LIKE ‘%猫%’
若使用 SELECT * FROM [user] WHERE u_name LIKE ‘%三%猫%’
虽然能搜索出“三脚猫”,但不能搜索出符合条件的“张猫三”。
2,_ : 表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句:
比如 SELECT * FROM [user] WHERE u_name LIKE ‘三’
只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的;
再比如 SELECT * FROM [user] WHERE u_name LIKE ‘三__’;
只找出“三脚猫”这样name为三个字且第一个字是“三”的;
3,[ ] :表示括号内所列字符中的一个(类似正则表达式)。指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。
比如 SELECT * FROM [user] WHERE u_name LIKE ‘[张李王]三’
将找出“张三”、“李三”、“王三”(而不是“张李王三”);
如 [ ] 内有一系列字符(01234、abcde之类的)则可略写为“0-4”、“a-e”
SELECT * FROM [user] WHERE u_name LIKE ‘老[1-9]’
将找出“老1”、“老2”、……、“老9”;
4,[^ ] :表示不在括号所列之内的单个字符。其取值和 [] 相同,但它要求所匹配对象为指定字符以外的任一个字符。
比如 SELECT * FROM [user] WHERE u_name LIKE ‘[^张李王]三’
将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等;
SELECT * FROM [user] WHERE u_name LIKE ‘老[^1-4]’;
将排除“老1”到“老4”,寻找“老5”、“老6”、……
5,查询内容包含通配符时
由于通配符的缘故,导致我们查询特殊字符“%”、“_”、“[”的语句无法正常实现,而把特殊字符用“[ ]”
括起便可正常查询。据此我们写出以下函数:
function sqlencode(str)
str=replace(str,"[","[[]") '此句一定要在最前
str=replace(str,"","[]")
str=replace(str,"%","[%]")
sqlencode=str
end function
6,查看所有列定义
show full column from student;
alter table student add constraint PK_1
primary key(id);
DESC student;
7,检查约束
ALTER TABLE userInfo
ADD CONSTRAINT shenf
CHECK (len(PID)>=15 ||len(PID)<=18 );
ALTER TABLE userInfo
ADD CONSTRAINT dianhua
CHECK (len(telephone)=11);
默认约束
alter table 表名 alter 字段名 set default 默认数据