千锋教育2218 MYSQL

[TOC]

8:00 之前进班

晚自习10：00

Day26 笔记 -- Mysql

数据库入门

SQL：结构化查询语言

DDL：数据定义语言

DML：数据操作语言

DCL：数据控制语言

TCL：事务控制语言

数据库的连接：

mysql -u用户名 -p密码

C:\WINDOWS\system32>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye

 
C:\WINDOWS\system32>mysql -uroot -p410221
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.    

数据库管理语句

创建数据库

create database 数据库名 charset 字符集编码 collate 校队规则（排序规则）

如果不指定字符集、使用安装 mysql 时设置的字符集

mysql> create database db2220;
Query OK, 1 row affected (0.00 sec)

删除数据库

drop database 数据库名

mysql> drop database dbtest;
Query OK, 0 rows affected (0.01 sec)

展示所有的数据库

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bdtest             |
| company            |
| db_2220            |
| javaee_2218        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

展示数据库创建信息

show create database 数据库名

mysql> show create database db2220;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| db2220   | CREATE DATABASE `db2220` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

修改数据库

alter database 数据库 charset.... collate...

mysql> alter database db2220 default charset gb2312;
Query OK, 1 row affected (0.00 sec)

切换数据库

use 数据库名

mysql> use db2220;
Database changed

表管理的操作语句

展示所有的表

show tables

mysql> show tables;
Empty set (0.00 sec)

创建表

create table 表名（列名 1 类型， ....）

mysql> create table student(id int, name varchar(20), age int);
Query OK, 0 rows affected (0.04 sec)

数据类型：

数值：int/bigint/double/decimal
int(11) 数字表示数据的显示宽度，需要配合无符号属性和zerofill一起使用
decimal 表示确定精度的小数
字符串、文本等：
char() 固定长度，最大255字符
varchar() 长度可变，最大65535字符
text...
blob...
日期时间：
date
datetime 常用
timestamp 照样是按照年月日时分秒进行展示

显示表结构

desc student;

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除表

drop table 表名

mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)

修改表

添加列

alter table 表名 add column 列名 类型

mysql> alter table student add column sex char(2);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除列

alter table 表名 drop column 列名

mysql> alter table student drop column sex;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改列的类型

alter table 表名 modify column 列名 新的类型

mysql> alter table student modify column sex varchar(2);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改列名

alter table 表名 change column 旧列名 新列名 类型

mysql> alter table student change column sex gender varchar(1);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

重命名表

rename table 旧表名 to 新表名

mysql> rename table student to stu;
Query OK, 0 rows affected (0.01 sec)

增删改操作

insert

insert into 表名（列名,.......） values （值, ....）

列名和值的个数、顺序、类型一定要保持一致

如果给所有列添加数据、列名可以省略、需要注意值的个数和顺序

同一个 insert 语句、也可以一次插入多条记录

-- values和列名个数、顺序、类型要保持一致
mysql> insert into stu(id,name,age,gender) values(1,'Jobs',50,'男');
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu(name,id,age,gender) values('Jordan',2,55,'男');
Query OK, 1 row affected (0.00 sec)

-- 如果给所有字段插入数据，列名可以省略，但是values后面数据，顺序要和创建表的是顺序一致
mysql> insert into stu values(10,'张三',23,'女');
Query OK, 1 row affected (0.01 sec)

-- 可以针对某些列添加数据
mysql> insert into stu(id,name) values(6,'李四');
Query OK, 1 row affected (0.00 sec)

-- 插入多条记录
mysql> insert into stu(id,name,age,gender) values(12, 'Tom', 12, '男'),(13, 'Jerry', 10, '男');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- 报错
mysql> insert into stu(id,name,age,gender) values(12, 'Tom', 12, '男邢');
ERROR 1406 (22001): Data too long for column 'gender' at row 1

delete

delete from 表名 where 条件

mysql> delete from stu where id=1;
Query OK, 0 rows affected (0.00 sec)

-- 删除表中所有数据
mysql> delete from stu;
Query OK, 5 rows affected (0.00 sec)

截断表：truncate 表名

mysql> truncate stu;
Query OK, 0 rows affected (0.03 sec)

delete删除可以通过事务的回滚进行恢复、 truncate 不能针对自增值、delete 不会清空自增的记录、truncate 会

update

update 表名 set 列 = 值，...... where 条件

-- 更新符合条件的记录
mysql> update stu set age=30 where id=12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 不写条件，所有记录都会更新
mysql> update stu set age=25;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

查询语句

查询所有记录

select * from 表名

根据列名进行查询

select 列名，..... from ....

mysql> select id,name,age+10 from stu;
+------+-------+--------+
| id   | name  | age+10 |
+------+-------+--------+
|   12 | Tom   |     35 |
|   13 | Jerry |     40 |
+------+-------+--------+
2 rows in set (0.00 sec)

列别名

select 列 as 别名....

as 可以省略

mysql> select id,name sname from stu;
+------+-------+
| id   | sname |
+------+-------+
|   12 | Tom   |
|   13 | Jerry |
+------+-------+
2 rows in set (0.00 sec)

mysql> select id as sid,name sname from stu;
+------+-------+
| sid  | sname |
+------+-------+
|   12 | Tom   |
|   13 | Jerry |
+------+-------+
2 rows in set (0.00 sec)

针对列可以进行基本算数运算

针对数值型字段

mysql> select id,name,age+10 from stu;
+------+-------+--------+
| id   | name  | age+10 |
+------+-------+--------+
|   12 | Tom   |     35 |
|   13 | Jerry |     40 |
+------+-------+--------+
2 rows in set (0.00 sec)

mysql> select id,name,age+10 new_age from stu;
+------+-------+---------+
| id   | name  | new_age |
+------+-------+---------+
|   12 | Tom   |      35 |
|   13 | Jerry |      40 |
+------+-------+---------+
2 rows in set (0.00 sec)

mysql> select id,name,age+id from stu;
+------+-------+--------+
| id   | name  | age+id |
+------+-------+--------+
|   12 | Tom   |     37 |
|   13 | Jerry |     43 |
+------+-------+--------+
2 rows in set (0.00 sec)

去重 distinct

distinct 后可以跟一个或多个字段

只有多个字段的值都相同时，才属于重复数据

mysql> select distinct age from stu;
+------+
| age  |
+------+
|   25 |
|   30 |
|   24 |
|   19 |
+------+
4 rows in set (0.00 sec)

mysql> select * from stu;
+------+--------+------+--------+
| id   | name   | age  | gender |
+------+--------+------+--------+
|   12 | Tom    |   25 | 男     |
|   13 | Jerry  |   30 | 男     |
|    1 | haha   |   25 | 男     |
|    2 | hehe   |   25 | 女     |
|    5 | heihei |   19 | NULL   |
+------+--------+------+--------+
5 rows in set (0.00 sec)

-- distinct后如果使用多个字段，只有当这些字段的数据都相同时，才认为重复
mysql> select distinct age,gender from stu;
+------+--------+
| age  | gender |
+------+--------+
|