MySQL学习
MySQL语法
一 事务
针对数据库多次DML操作的时候,保证数据完整性的一种手段
1 ACID
A:Aotomicity:原子性。不可被分割。也就是说事务中的所有操作要么成功,要么失败。
C:Consistency:一致性。事务执行之后,数据库的状态或者其他业务的规则保持一致。
I:Isolation:隔离性。指在并发操作中,不同的事务之间应该是隔离开来的,各事务之间互不干扰。
D:Durability:持久性。事务一旦提交成功,将不能回滚。
2 事务中产生的问题
事务中3中读的问题:
脏读: 读取到另外一个事务未提交的数据 ****脏读必须要解决*****
不可重复读: 对同一记录的两次读取不一致,另一事务对改记录进行了修改
幻读/虚读: 对同一张表的两次查询不一致,因为另一事务插入了一条记录
3 事务隔离级别
串行化: 避免事务中出现的所有问题,但是效率低
可重复读: 性能比串行化好,可以避免脏读和不可重复读,不可避免幻读,ORACLE的默认隔离级别
读已提交数据: 性能比可重复读好,可以避免脏读,MySQL的默认隔离级别
读未提交数据: 性能最好,但是任何问题都不能避免
4 手动提交事务
开启事务: START TRANSACTION
结束事务: COMMIT 或 ROLLBACK
二 DDL
1 show
show databases; -- 查询所有数据库
show tables; --查询所有表
2 create
-- 创建字符集为utf8的数据库zxy
create database if not exists zxy
character set = utf8;
-- 选用数据库
use zxy;
-- 创建表
create table if not exists zxy.user(
id int,
name varchar(20)
);
3 drop
drop database if exists zxy; -- 删除数据库
drop table if exists zxy.user; --删除数据表
4 alter
alter database zxy character set utf32; --修改字符集
alter table zxy.user rename users; --修改表名
alter table zxy.user modify id TINYINT NOT NULL,change name uname varchar(25); --修改列属性,列名
alter table zxy.user add age timestamp; -- 添加年龄列
alter table zxy.user drop column age; -- 删除列
三 DML
1 insert
insert into zxy.user(id,name) values(2,'zxy'); --向id,name插入值
2 update
update zxy.user set name = 'zxy'
where id = '1';
-- 将id=1的学生对应的name改为zxy
3 delete
delete from zxy.user; --删除全表
truncate table zxy.user;--删除全表记录
delete from zxy.user where id = 1; --删除指定条件的记录
四 DCL
1 分配权限
-- 分配权限
grant all privileges on *.* to 'root'@'%' identified by '111213' with grant option;
-- 刷新权限
flush privileges;
五 DQL
1 where
select * from user where id = 1;
-- 在where后的条件合理的情况下,可以查询到结果
-- 仅提供思路
-- 例如:
name = 'zxy';
id > 100;
sex = 'man' and name = 'zxy';
sex = 'man' or name = 'zxy';
id between 1 and 5
id in (1,2,3,4,5)
id not in (1,2,3,4,5)
2 like
-- %表示多个字符的占位符
select * from zxy.user
where name like 'z%';
-- _表示单个字符的占位符
select * from zxy.user
where name like 'zx_';
3 distinct
-- 去重查询
select distinct name from zxy.user;
4 ifnull
-- 如果为空,设置默认值
select id,name,ifnull(age,'0') from zxy.user;
5 count
-- 计算个数
select count(*) from zxy.user;
6 max
-- 统计出最大的数
select max(age) from zxy.user;
7 min
-- 统计出最小的数
select min(age) from zxy.user;
8 sum
-- 统计出总数
select count(age) from zxy.user;
9 group by
-- 按条件分组查询
select id,name,gender,sum(ifnull(salary,0)) as sum
from zxy.user
group by gender
having id < 100;
10 limit
-- limit n
select * from zxy.user limit 1; -- 只查询第一条
-- limit m,n
select * from zxy.user limit 0,2; --从下标索引0处也就是第一行开始,往后取5行输出
select * from zxy.user limit 1,1; --取到第二条数据
11 order by
-- 查询用户信息,按年龄降序,女士有限原则
select * from zxy.user
order by age desc,gender asc;
六 约束
1 primary key
- 非空
- 唯一
- 能被外键所引用
-- 方式一
create table if not exists zxy.user(
id int primary key,
name varchar(20)
);
-- 方式二
create table if not exists zxy.user(
id int,
name varchar(20)
primary key(id)
);
1.1 alter … add
alter table zxy.user add primary key(id);
1.2 alter … drop
alter table zxy.user drop primary key(id);
1.3 自增长
create table if not exists zxy.user(
id int AUTO_INCREMENT,
name varchar(20),
primary key(id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;
2 foreign key
create table dept (
id int(11) not null,
name varchar(25) default null,
primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
-- 建表的同时,简历dept表的id的外键
create table emp (
id int(11) not null,
name varchar(25) defalut null,
deptid int(11) not null,
primary key (id),
CONSTRAINT emp_dept FOREIGN KEY(deptid) references dept(id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
2.1 alter … add
alter table zxy.user add constraint emp_dept foreign key(deptid) references dept(id);
2.2 alter … drop
alter table zxy.user drop foreign key emp_dept;
3 unique
create table user.zxy (
id int(11) not null,
name varchar(25) unique
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
4 Having和Where
HAVING后面可以写聚合函数、WHERE后面不可以写聚合函数
HAVING是对分组后的数据做的过滤,WHERE是对分组前的数据做的过滤
> WHERE是对分组千的数据做过滤,如果没有满足WHERE条件的过滤,将不参加分组
> HAVING是分组后的数据做过滤
5 去重distinct
select distinct deptno from emp
6 primary key主键设置
alter table stu add primary key(id)
7 unique 唯一性约束
alter table tableName add unique(username)
8 域完整性
alter table tableName modify colName type not null;
9 多重循环:
SELECT *
FROM emp
WHERE sal > (
SELECT sal FROM emp
WHERE empno = 7566
)
-- 查询工资大于10号部门的平均工资的非10号部门的员工信息
SELECT *
FROM emp
WHERE sal >(
SELECT AVG(
IFNULL(sal,0)
)
FROM emp
WHERE deptno = 10
)
AND deptno !=10
SELECT *,CASE
WHEN sal >= 5000 THEN '第一等级'
WHEN sal >= 3000 THEN '第二等级'
ELSE '其他'
END levels
FROM emp
SELECT ename ,CASE deptno
WHEN 10 THEN '第一部门'
WHEN 20 THEN '第二部门'
ELSE '其他'
END
FROM emp
10 时钟日历函数
SELECT CURDATE();
SELECT CURTIME();
SELECT NOW();
SELECT SYSDATE();
SELECT CURRENT_TIMESTAMP();
SELECT DAYOFMONTH();
SELECT WEEKDAY(NOW());
SELECT DAYNAME('2020-06-04')
SELECT DAYOFWEEK(NOW())
SELECT DAYOFYEAR(NOW())
SELECT MONTHNAME(NOW())
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())
SELECT DATE_FORMAT(NOW(),'%y/%m/%d %h:%i:%s %p %w')
SELECT ADDDATE(NOW(),INTERVAL 3 DAY)
SELECT DATE_ADD(NOW(),INTERVAL 3 DAY)
SELECT DATE_SUB(NOW(),INTERVAL 3 DAY)
SELECT SUBDATE(NOW(),INTERVAL 3 MONTH)
11 字符串函数
-- 字符串函数
SELECT CONV(6,10,2) -- 把数字6从10进制转为2进制
SELECT CONCAT('hello','world')
SELECT LPAD('aaaaaaaaa',12,'k'),RPAD('bbbbbbbb',12,'k')
SELECT LEFT('aasdaddadas',6), RIGHT('dsdsdd',6)
SELECT SUBSTRING('heloworld',6,3)
SELECT LENGTH('你好'),OCTET_LENGTH('大家好') -- 输出字节长度
SELECT CHAR_LENGTH('你好'),CHARACTER_LENGTH('大家好') -- 输出字符个数
SELECT LTRIM(' hello'),RTRIM('ff '),TRIM(' ff ')-- 补空,左靠齐
SELECT LCASE('ABS'),LOWER('ASD'),UCASE('ass'),UPPER('sss') -- 大写,小写
SELECT REPLACE ('hello world','world','hello') -- 替换
SELECT INSERT('hello wworld',7,3,'aaa')
12 not null
create table user.zxy (
id int(11) not null,
name varchar(25) unique
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
七 多表查询
将多个表的查询结果连接在一起
create table zxy.user (
id int,
name varchar(50),
score varchar(50)
);
create table zxy.stu (
id int,
date timestamp
);
1 union
-- 1.union 合并去除
select id,name from zxy.user
union
select id,name from zxy.user;
2 union all
-- 1.union 合并但不去重
select id,name from zxy.user
union all
select id,name from zxy.user;
3 inner join
-- 只有等式两边成立才能将结果查询出来
select
user.id,
user.name,
user.score,
stu.date
from user inner join stu
on user.id = stu.id;
4 left join
-- 只有等式两边成立才能将结果查询出来
select
user.id,
user.name,
user.score,
stu.date
from user left inner join stu
on user.id = stu.id;
5 right join
-- 只有等式两边成立才能将结果查询出来
select
user.id,
user.name,
user.score,
stu.date
from user right inner join stu
on user.id = stu.id;
6 natural join
-- 自然内连接查询
-- ON条件取笛卡尔积有点多余:
-- 1.要求外键名称和你另一张表的主键名称必须完全相同
-- 2.主外键约束
select
user.id,
user.name,
user.score,
stu.date
from user natural inner join stu
八 JDBC
驱动:mysql-connector-java-5.1.7-bin.jar
package com.zxy.demo.Pojo;
import java.sql.*;
public class MySQL {
public static Connection getConnection() throws ClassNotFoundException, SQLException {
/**
* 参数1:驱动 com.mysql.jdbc.Driver
* 参数2: URL jdbc:mysql://localhost:3306/zxy
* 参数3:用户名 root
* 参数4:密码 root
*/
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象
String url = "jdbc:mysql://localhost:3306/zxy";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
public static void close(Statement statement, Connection connection) throws SQLException {
if (statement != null && connection != null){
statement.close();
connection.close();
}
}
/**
* 测试
* @param args
*/
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//获得connection
Connection connection = MySQL.getConnection();
//SQL语句
String sql = "select * from zxy.user";
//执行对象
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
//遍历查询
while (resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
System.out.println(id + "," + name);
}
//关闭连接
MySQL.close(statement,connection);
}
}
九、Win操作指令
1.登录:
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uroot -p
Enter password: ****
2.重置密码:
C:\Users\ZXY>mysqladmin -uroot -pzxy password root
mysql> set password for root@localhost = password('root');
Query OK, 0 rows affected, 1 warning (0.00 sec)
3.密码忘记:
如果密码忘记,可以用notepad++打开my.ini文件,在[mysqld]后面加上skip-grant-tables
然后利用第三种修改密码的方式,修改密码后,再把这一行密码删除
C:\Users\ZXY>cd C:\Program Files\MySQL\MySQL Server 5.7\bin
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server 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 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> creat database dbl:zzz
-> creat database dbl:
-> creat database dbl;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creat database dbl:zzz
creat database dbl:
creat database dbl' at line 1
mysql> create database dbl;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbl |
| mysql |
| performance_schema |
| sakila |
| sys |
| video |
| world |
+--------------------+
8 rows in set (0.01 sec)
mysql> show create database dbl;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| dbl | CREATE DATABASE `dbl` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> use dbl;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| dbl |
+------------+
1 row in set (0.00 sec)
mysql> use video;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| video |
+------------+
1 row in set (0.00 sec)
mysql> drap database dbl;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'drap database dbl' at line 1
mysql> show
-> databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbl |
| mysql |
| performance_schema |
| sakila |
| sys |
| video |
| world |
+--------------------+
8 rows in set (0.00 sec)
mysql> create database zxy
-> create database zxy;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create database zxy' at line 2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbl |
| mysql |
| performance_schema |
| sakila |
| sys |
| video |
| world |
+--------------------+
8 rows in set (0.00 sec)
mysql> create database zxy;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbl |
| mysql |
| performance_schema |
| sakila |
| sys |
| video |
| world |
| zxy |
+--------------------+
9 rows in set (0.00 sec)
mysql> use zxy;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table users{
->
->
->
-> exit
-> create table users{;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{
exit
create table users{' at line 1
mysql> create table users(
-> name varchar(50),
-> age int,
-> gender varchar(10)
-> height int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'height int)' at line 5
mysql> create table users(
-> name varchar(50),
-> age int,
-> gender varchar(10),
-> height int);
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
+---------------+
| Tables_in_zxy |
+---------------+
| users |
+---------------+
1 row in set (0.00 sec)
mysql> desc users;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
| height | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
mysql> alter table users add adress varchar(10);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc users;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
| height | int(11) | YES | | NULL | |
| adress | varchar(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table users modify adress address;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'address' at line 1
mysql> alter table users modify adress varchar(9);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table users drop adress;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc users;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
| height | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table users change gender sex varchar(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc users;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
| height | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> drop table users;
Query OK, 0 rows affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbl |
| mysql |
| performance_schema |
| sakila |
| sys |
| video |
| world |
| zxy |
+--------------------+
9 rows in set (0.00 sec)