数据库基本操作SQL

1、数据库增删改

# 创建表
mysql> create table t1(id int,
name varchar(10),
sex enum('man','gril'),
age int);

# 插入数据
#1.插⼊完整数据, 顺序插⼊: INSERT INTO 表名(字段1,字段2,字段n) VALUES (值1,值2,值 n);
mysql> insert into t1(id,name,sex,age) values ("1","wing","man","18");
Query OK, 1 row affected (0.01 sec)

#2.插⼊完整数据, 推荐⽅式 INSERT INTO 表名 VALUES (值1,值2,值n);
mysql> insert into t1 values("2","wing1","gril","10");
Query OK, 1 row affected (0.01 sec)

#3.指定字段插⼊, INSERT INTO 表名(字段2,字段3…) VALUES (值 2,值3…);
mysql> insert into t1(name,sex,age) values ("wing2","man","20");
Query OK, 1 row affected (0.00 sec)

#4.插⼊多条记录, INSERT INTO 表名 VALUES (值1,值2,值n),(值1,值2,值n);
mysql> insert into t1 values
("3","wing3","man","18"),
("4","wing4","man","18"),
("5","wing5","man","18");

# 更新数据update语句
mysql> update t1 set name="update_w1" where name="wing1";

# 删除数据
mysql> delete from t1 where name="update_w1";

2、修改数据库密码

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *4927A5B79C852EA2CE585A7679C5C26DF683F18C |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
//更新字段
mysql> update mysql.user set
authentication_string=password("Wing@123")
where user='root' and host='localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql> flush privileges;

3、数据库查询语句

//查看表字段与表信息
mysql> desc t2;
+--------+--------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('man','gril') | NO | | man | |
| time | date | NO | | NULL | |
| post | varchar(50) | NO | | NULL | |
| job | varchar(100) | YES | | NULL | |
| salary | double(15,2) | NO | | NULL | |
| office | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+--------------------+------+-----+---------+----------------+

#2.指定字段查询
mysql> select name,salary,dep_id from t2;

#3.避免重复查询字段distinct
mysql> select distinct post from t2;

#4.设置别名。计算年薪并定义输出字段信息别名, AS可去掉
mysql> select name,salary,salary*14 AS Annual_salary from t2;
+-------+----------+---------------+
| name | salary | Annual_salary |
+-------+----------+---------------+
| jack  | 5000.00   | 70000.00 |
| tom   | 5500.00   | 77000.00 |
| robin | 8000.00   | 112000.00 |
| alice | 7200.00   | 100800.00 |
| wing  | 600.00    | 8400.00 |
| harry | 6000.00   | 84000.00 |
| trf   | 20000.00  | 280000.00 |
| test  | 2200.00   | 30800.00 |
| dog   | 2200.00   | 30800.00 |
| alex  | 2200.00   | 30800.00 |
+-------+----------+---------------+

#5.定义显示格式 CONCAT() 函数⽤于连接字符串
mysql> select concat(name,' annual salary:',salary*14) from t2;
+------------------------------------------+
| concat(name,' annual salary:',salary*14) |
+------------------------------------------+
| jack annual salary:70000.00 |
| tom annual salary:77000.00 |
| robin annual salary:112000.00 |
| alice annual salary:100800.00 |
| wing annual salary:8400.00 |
| harry annual salary:84000.00 |
| trf annual salary:280000.00 |
| test annual salary:30800.00 |
| dog annual salary:30800.00 |
| alex annual salary:30800.00 |
+------------------------------------------+

# 1.单条件查询
mysql> select name,post from t2 where post='hr';
+-------+------+
| name | post |
+-------+------+
| wing | hr |
| harry | hr |

#2.多条件查询
mysql> select name,post,salary from t2 where post='hr' and salary >5000;
+-------+------+---------+
| name | post | salary |
+-------+------+---------+
| harry | hr | 6000.00 |

#3.查找薪资范围在8000-2000,使⽤BETWEEN区间
mysql> select name,salary from t2 where salary between 8000 and 20000;
+-------+----------+
| name | salary |
+-------+----------+
| robin | 8000.00 |
| trf | 20000.00 |
+-------+----------+

#4.查找部⻔为Null, 没有部⻔的员⼯
mysql> select name,job from t2 where job is null;
+-------+------+
| name | job |
+-------+------+
| harry | NULL |
| dog | NULL |
+-------+------+

# 查看部⻔为空的员⼯
mysql> select name,job from t2 where job='';
+------+------+
| name | job |
+------+------+
| alex | |
+------+------+

#5.集合查询
mysql> select name,salary from t2 where salary=4000 OR salary=5000 OR salary=8000;
mysql> select name,salary from t2 where salary in(4000,5000,8000);
+-------+---------+
| name | salary |
+-------+---------+
| jack | 5000.00 |
| robin | 8000.00 |
+-------+---------+

#6.模糊查询like, 通配符%
mysql> select * from t2 where name like 'al%';
+----+-------+------+------------+------------+-------+---------+--------+--------+
| id | name | sex | time | post | job | salary | office | dep_id |
+----+-------+------+------------+------------+-------+---------+--------+--------+
| 4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 10 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
+----+-------+------+------------+------------+-------+---------+--------+--------+

#通配符__
mysql> select * from t2 where name like 'al__';
+----+------+-----+------------+------+------+---------+--------+--------+
| id | name | sex | time | post | job | salary | office | dep_id |
+----+------+-----+------------+------+------+---------+--------+--------+
| 10 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102

# 查询排序
#1.按单列排序, 按薪⽔从低到⾼排序, 默认ASC（表示升序排列）
mysql> select * from t2 ORDER BY salary ASC;


# 按单列排序, 薪⽔从低往⾼排序, DESC表示倒序
mysql> select * from t2 ORDER BY salary DESC;

#2.多列排序, 先按⼊职时间，再按薪⽔排序
mysql> select * from t2 ORDER BY time DESC, salary ASC;

#3.多列排序, 先按职位， 再按薪⽔排序
mysql> select * from t2 ORDER BY post, salary DESC;

#4.查询薪资最⾼前5名同事, 默认初始位置为0
mysql> select * from t2 ORDER BY salary DESC limit 5;

#5. 从第4条开始, 并显示5条数据
mysql> select * from t2 ORDER BY salary DESC limit 3,5;

## 使⽤集合函数查询
#统计当前表总共多少条数据
mysql> select count(*) from t2;

#统计dep_id为101有多少条数据
mysql> select count(*) from t2 where dep_id=101;

#薪⽔最⾼
mysql> select MAX(salary) from t2;

#薪⽔最低
mysql> select min(salary) from t2

#平均薪⽔
mysql> select avg(salary) from t2;

#总共发放多少薪⽔
mysql> select sum(salary) from t2;

#hr部⻔发放多少薪⽔
mysql> select sum(salary) from t2 where post='hr';

#哪个部⻔哪个⼈薪⽔最⾼
mysql> select * from t2 where salary=(select max(salary) from t2);

## 分组查询
# GROUP BY 和 GROUP_CONCAT()函数⼀起使⽤
mysql> select post,GROUP_CONCAT(name) from t2 GROUP BY post;

# 使用正则表达式查询
mysql> select * from t2 where name REGEXP '^ali';