常用Mysql知识

1.常用查看命令

1.mysql登录命令

mysql -u (用户名) -h (mysql服务所在地址) -P (可选默认3306 指定端口号) -p
mysql -u root -h 127.0.0.1 -P3306 -p

2.show databases; 查看数据库
3.use databases; 进入数据库
4.show tables; 查看当前所在数据库下面的所有表
5.show columns from mysql.db 查看mysql数据库下面的db表的字段
6.可以用describe命令察看刚建立的表结构。
mysql> describe name;
7.mysql服务的启动和停止
net stop mysql
net start mysql

总结 show命令是查看数据库或者表的结构（show不会给你查看数据）

2.表结构更改

2.1属性与约束

属性	含义
null	空
not null	不为空
default	默认值如： 上面的 age : age int default 18
unique key	唯一 设置 某个列 的值都是唯一的，也就是没用重复,如 身份证号，一般是唯一的
primary key	主键唯一标示(自带唯一属性，not null属性)一个表中必须有的,一般都是数字自增
auto_increment	自增长 必须给主键设置 ，int ,它 的数值是不会回退的
foreign key	外键 减少冗余，用来与其他表连接。

语法： constraint 你给外键取的名字 foreign key (你想引用到外键的列名称) references 参考表的表名(列名称且这个列名称是有主键属性)
ALTER TABLE tabname ADD PRIMARY KEY ( 列名);

2.2更改表结构

更改表名称
语法：alter table (旧表名) rename as (新表名)
如：-> alter table class1 rename as classOne;

添加字段
语法：alter table (表名称) add (字段名) (列类型)[属性][约束]
如:-> alter table class2 add phone varchar(20);

删除字段
语法：alter table (表名称) drop (字段名)

更改字段名称
语法：alter table (表名) change (旧字段名) (新字段名)(列类型)[属性][约束]
如：-> alter table class2 change name stu_name varchar(20) not null;

更改属性：
语法： alter table (表名称) modity (字段名) ( 列类型) [属性][约束]
如:-> alter table class2 modify stu_name varchar(50) not null;

增加外键：
语法： alter table (你要增加外键的表名) add constraint (你给外键取的名字)( foreign key (你想引用到外键的列名称) references (参考表的表名)(列名称且这个列名称是有主键属性)

3.删/建 库,表

1.建立数据库：
CREATE DATABASE 库名;

2.建立数据表：
mysql> USE 库名;
mysql> CREATE TABLE 表名 (字段名 VARCHAR(20), 字段名 CHAR(1));
例如：在刚创建的aaa库中建立表name,表中有id(序号，自动增长)，xm（姓名）,xb（性别）,csny（出身年月）四个字段
mysql> use aaa;
mysql> create table name (id int(3) auto_increment not null primary key,
mysql> xm char(8),
mysql> xb char(2),
mysql> csny date);
可以用describe命令察看刚建立的表结构。
mysql> describe name;
3.删除数据库：
mysql> DROP DATABASE 库名;

4.删除数据表：
mysql> DROP TABLE 表名;

5.导入.sql文件命令：
mysql> USE 数据库名;
mysql> SOURCE d:/mysql.sql;

4.基本增删改查Sql

【Msql基础知识】
1.新增记录语句
语法格式：
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,…)]
{VALUES | VALUE} ({expr | DEFAULT},…),(…),…
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] … ]
常用举例
INSERT member(name,age，reg_date) values(‘张三’,20,’2009-11-30’);

2.删除记录语句
语法格式：
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY …]
[LIMIT row_count]
常用举例
–删除姓名为李四的会员记录
DELETE FROM member WHERE name = ‘李四’;

3.更新记录语句
语法格式：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] …
[WHERE where_condition]
[ORDER BY …]
[LIMIT row_count]
常用举例
–更新姓名为张三的会员年龄为19岁
UPDATE member SET age = 19 WHERE name = ‘张三’;

4.查询语句
语法格式：
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUN
select_expr [, select_expr …]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], … [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], …]
[LIMIT {[offset,] row_count | row_count OFF
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE ‘file_name’ export_options
| INTO DUMPFILE ‘file_name’
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
常用举例
查询member表中姓名叫张三的会员
SELECT * FROM member WHERE name = ‘张三’;
查询member表中所有姓张的会员
SELECT * FROM member WHERE name like ‘张%’;

5.存储过程及其使用

【—mysql存储过程学习笔记—】
总结的非常详细，简单明了，一起参考学习啦。

6.示例

【—Mysql常见测试面试题—】
【MySQL经典50道通关练习题】
下面我搬运几条我觉得必须要掌握的：

示例1

1.用一条SQL 语句 查询出每门课都大于80 分的学生姓名

id	name	course	score
1	张三	语文	81
2	张三	数学	75
3	莉斯	语文	76
4	莉斯	数学	90
5	王五	语文	81
6	王五	数学	100
7	王五	英语	90

数据准备：首先我们先创建这样的一个表

mysql> create table stu_Score(
    -> id int not null primary key auto_increment,
    -> name varchar(5) not null,
    -> course varchar(2) not null,
    -> score int(3)
    -> );

然后插入数据，这里建了一个存储过程，方便再次使用。

mysql> create procedure insertDemo()
    -> begin
    -> insert into stu_Score(name,course,score) values('张三','语文',81);
    -> insert into stu_Score(name,course,score) values('张三','数学',75);
    -> insert into stu_Score(name,course,score) values('莉斯','语文',76);
    -> insert into stu_Score(name,course,score) values('莉斯','数学',90);
    -> insert into stu_Score(name,course,score) values('王五','语文',81);
    -> insert into stu_Score(name,course,score) values('王五','数学',100);
    -> insert into stu_Score(name,course,score) values('王五','英语',90);
    -> end;
    
mysql> call insertDemo();
Query OK, 1 row affected
mysql> select * from stu_Score;

答案：

select distinct name from stu_Score where name not in(select name from stu_Score where score<=80);
select name from stu_Score group by name having min(score)>80;

示例2

表数据，依然是如上的stu_Score 表，前7条不变，我们再添加一条重复的数据；
insert into stu_Score(name,course,score) values(‘张三’,‘语文’,81);
即数据如下，问题：删除 “除了自动id不同, 其他都相同的学生冗余信息”。

mysql> select * from stu_Score;
+----+------+--------+-------+
| id | name | course | score |
+----+------+--------+-------+
|  1 | 张三 | 语文   |    81 |
|  2 | 张三 | 数学   |    75 |
|  3 | 莉斯 | 语文   |    76 |
|  4 | 莉斯 | 数学   |    90 |
|  5 | 王五 | 语文   |    81 |
|  6 | 王五 | 数学   |   100 |
|  7 | 王五 | 英语   |    90 |
| 10 | 张三 | 语文   |    81 |
+----+------+--------+-------+
8 rows in set

答案：

mysql> delete from stu_Score where id not in(
    -> select t.id from
    -> (select min(id) as id from stu_Score group by name,course,score)t
    -> );

示例3

数据结构如上表，新增两条不及格分数。

mysql> insert into stu_Score(name,course,score) values('小芒果','语文',59);
mysql> insert into stu_Score(name,course,score) values('小芒果','数学',39);
mysql> insert into stu_Score(name,course,score) values('小芒果','英语',149);
mysql> select * from stu_score;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  1 | 张三   | 语文   |    81 |
|  2 | 张三   | 数学   |    75 |
|  3 | 莉斯   | 语文   |    76 |
|  4 | 莉斯   | 数学   |    90 |
|  5 | 王五   | 语文   |    81 |
|  6 | 王五   | 数学   |   100 |
|  7 | 王五   | 英语   |    90 |
| 10 | 张三   | 语文   |    81 |
| 11 | 小芒果 | 语文   |    59 |
| 12 | 小芒果 | 数学   |    39 |
| 13 | 小芒果 | 英语   |   149 |
+----+--------+--------+-------+

问题：如何将如上表使用mysql语句查询得如下结果
±-------±------±-------±-----+
| name | score | course | mark |
±-------±------±-------±-----+
| 张三 | 81 | 语文 | pass |
| 张三 | 75 | 数学 | pass |
| 莉斯 | 76 | 语文 | pass |
| 莉斯 | 90 | 数学 | pass |
| 王五 | 81 | 语文 | pass |
| 王五 | 100 | 数学 | pass |
| 王五 | 90 | 英语 | pass |
| 张三 | 81 | 语文 | pass |
| 小芒果 | 59 | 语文 | fail |
| 小芒果 | 39 | 数学 | fail |
| 小芒果 | 149 | 英语 | pass |
±-------±------±-------±-----+
答案：

mysql> select name,score,course,(case when score<60 then 'fail' else 'pass' end) as mark from stu_score;

示例4

查询所有1-12月份的借额(debit)比101科目(accid)相对应1-12月份的借额高的科目,请注意：testdb中有很多科目，都有1 －12 月份的借额。accid ：科目代码，occmon ：借额月份，debit：借额
建表创建数据如下：

mysql> select * from testdb;
+----+-------+--------+-------+
| id | accid | occmon | debit |
+----+-------+--------+-------+
|  1 |   101 |      1 |   120 |
|  2 |   101 |      2 |   770 |
|  3 |   101 |      3 |   470 |
|  4 |   102 |      1 |   670 |
|  5 |   102 |      2 |   578 |
|  6 |   102 |      3 |  1215 |
|  7 |   103 |      1 |  1015 |
|  8 |   103 |      2 |   201 |
|  9 |   103 |      3 |   801 |
+----+-------+--------+-------+

mysql> create table testdb(
    -> id int not null primary key auto_increment,
    -> accid int(3) not null,
    -> occmon int(2) not null,
    -> debit int(5)
    -> );
Query OK, 0 rows affected

mysql> create procedure insert_testdb()
    -> begin
    -> insert into testdb (accid,occmon,debit) values(101,1,120);
 	-> insert into testdb (accid,occmon,debit) values(101,2,770);
 	-> insert into testdb (accid,occmon,debit) values(101,3,470);
 	-> insert into testdb (accid,occmon,debit) values(102,1,670);
 	-> insert into testdb (accid,occmon,debit) values(102,2,578);
 	-> insert into testdb (accid,occmon,debit) values(102,3,1215);
 	-> insert into testdb (accid,occmon,debit) values(103,1,1015);
 	-> insert into testdb (accid,occmon,debit) values(103,2,201);
 	-> insert into testdb (accid,occmon,debit) values(103,3,801);
 	-> end;
 	mysql> call insert_testdb();

答案：

mysql> select a.* from testdb a,
    -> (select occmon,debit from testdb where accid=101)b
    -> where a.occmon=b.occmon and a.debit>b.debit;