SQL语句(twenty-three day)

一、SQL语句

1、新增

（1）insert into 库名称.表面

（2）insert into 表名称 values（1,"name","word"）

（3）insert into 表名称 select*from 其他表

（4）insert into 表 value（），（）

2、删除

delete from 表名       （全部删除）

delete from tablename where id=3

delete from tablename where age>3

delete from tablename where name on ("a"."b","c");

3、修改

（1）update mysql.user set host='%' where name='root'

（2）update user set password='abc' where username="xiaojiang"

4、查询

单表查询

        select 字段名列表 from 表明，索引

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+--------+--------+
| id | name   | gender |
+----+--------+--------+
|  1 | 张三   | 男     |
|  2 | 李四   | 男     |
|  3 | 包子   | 女     |
|  4 | 馒头   | 男     |
+----+--------+--------+
4 rows in set (0.00 sec)

mysql> select gender from student;
+--------+
| gender |
+--------+
| 男     |
| 男     |
| 女     |
| 男     |
+--------+
4 rows in set (0.00 sec)

mysql> select id,name from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 包子   |
|  4 | 馒头   |
+----+--------+
4 rows in set (0.00 sec)

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.07 sec)

mysql> select * from student as a,student as b;
+----+--------+--------+----+--------+--------+
| id | name   | gender | id | name   | gender |
+----+--------+--------+----+--------+--------+
|  4 | 馒头   | 男     |  1 | 张三   | 男     |
|  3 | 包子   | 女     |  1 | 张三   | 男     |
|  2 | 李四   | 男     |  1 | 张三   | 男     |
|  1 | 张三   | 男     |  1 | 张三   | 男     |
|  4 | 馒头   | 男     |  2 | 李四   | 男     |
|  3 | 包子   | 女     |  2 | 李四   | 男     |
|  2 | 李四   | 男     |  2 | 李四   | 男     |
|  1 | 张三   | 男     |  2 | 李四   | 男     |
|  4 | 馒头   | 男     |  3 | 包子   | 女     |
|  3 | 包子   | 女     |  3 | 包子   | 女     |
|  2 | 李四   | 男     |  3 | 包子   | 女     |
|  1 | 张三   | 男     |  3 | 包子   | 女     |
|  4 | 馒头   | 男     |  4 | 馒头   | 男     |
|  3 | 包子   | 女     |  4 | 馒头   | 男     |
|  2 | 李四   | 男     |  4 | 馒头   | 男     |
|  1 | 张三   | 男     |  4 | 馒头   | 男     |
+----+--------+--------+----+--------+--------+
16 rows in set (0.00 sec)

mysql> select id,username,password from student;
ERROR 1054 (42S22): Unknown column 'username' in 'field list'
mysql> select id,name,gender from student;
+----+--------+--------+
| id | name   | gender |
+----+--------+--------+
|  1 | 张三   | 男     |
|  2 | 李四   | 男     |
|  3 | 包子   | 女     |
|  4 | 馒头   | 男     |
+----+--------+--------+
4 rows in set (0.00 sec)

mysql> select id as 编号,name,gender from student;
+--------+--------+--------+
| 编号   | name   | gender |
+--------+--------+--------+
|      1 | 张三   | 男     |
|      2 | 李四   | 男     |
|      3 | 包子   | 女     |
|      4 | 馒头   | 男     |
+--------+--------+--------+
4 rows in set (0.00 sec)

mysql> select id as 编号,name as 姓名,gender as 性别 from student;
+--------+--------+--------+
| 编号   | 姓名   | 性别   |
+--------+--------+--------+
|      1 | 张三   | 男     |
|      2 | 李四   | 男     |
|      3 | 包子   | 女     |
|      4 | 馒头   | 男     |
+--------+--------+--------+
4 rows in set (0.00 sec)

远程连接数据库

（1）username

（2）password

（3）url mysql ip 数据库名称 端口

mysql> create user 'jiang'@'%' identified by 'Zhang@2002';;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'jiang'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

二、SQL进阶

创建表

mysql> create table product(
    -> id int primary key auto_increment,
    -> name varchar(45) not null,
    -> price float not null,
    -> qty int not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into product (name,price,qty) values("香蕉",8.5,200);
Query OK, 1 row affected (0.00 sec)

mysql> insert into product (name,price,qty) values("苹果",12.5,400);
Query OK, 1 row affected (0.00 sec)

mysql> insert into product (name,price,qty) values("菠萝",12.4,70);
Query OK, 1 row affected (0.01 sec)

mysql> insert into product (name,price,qty) values("哈密瓜",18.3,400);
Query OK, 1 row affected (0.00 sec)

mysql> insert into product (name,price,qty) values("西瓜",18.3,400);
Query OK, 1 row affected (0.01 sec)

mysql> select * from (select * from product order by qty) as a order by a.price;
+----+-----------+-------+-----+
| id | name      | price | qty |
+----+-----------+-------+-----+
|  1 | 香蕉      |   8.5 | 200 |
|  3 | 菠萝      |  12.4 |  70 |
|  2 | 苹果      |  12.5 | 400 |
|  4 | 哈密瓜    |  18.3 | 400 |
|  5 | 西瓜      |  18.3 | 400 |
+----+-----------+-------+-----+
5 rows in set (0.00 sec)

1、排序

select * from 表名 order by 要排序的列名

mysql> select * from product order by price;
+----+-----------+-------+-----+
| id | name      | price | qty |
+----+-----------+-------+-----+
|  1 | 香蕉      |   8.5 | 200 |
|  3 | 菠萝      |  12.4 |  70 |
|  2 | 苹果      |  12.5 | 400 |
|  4 | 哈密瓜    |  18.3 | 400 |
|  5 | 西瓜      |  18.3 | 400 |
+----+-----------+-------+-----+
5 rows in set (0.00 sec)

（1）降序

mysql> select * from product order by price desc;
+----+-----------+-------+-----+
| id | name      | price | qty |
+----+-----------+-------+-----+
|  4 | 哈密瓜    |  18.3 | 400 |
|  5 | 西瓜      |  18.3 | 400 |
|  2 | 苹果      |  12.5 | 400 |
|  3 | 菠萝      |  12.4 |  70 |
|  1 | 香蕉      |   8.5 | 200 |
+----+-----------+-------+-----+
5 rows in set (0.00 sec)

（2）升序

mysql> select * from product order by price asc;
+----+-----------+-------+-----+
| id | name      | price | qty |
+----+-----------+-------+-----+
|  1 | 香蕉      |   8.5 | 200 |
|  3 | 菠萝      |  12.4 |  70 |
|  2 | 苹果      |  12.5 | 400 |
|  4 | 哈密瓜    |  18.3 | 400 |
|  5 | 西瓜      |  18.3 | 400 |
+----+-----------+-------+-----+
5 rows in set (0.00 sec)

2、汇总

（1）count

查看最大/最小的数据（max/min）

mysql> select max(price) from product;
+------------+
| max(price) |
+------------+
|       18.3 |
+------------+
1 row in set (0.00 sec)

mysql> select min(price) from product;
+------------+
| min(price) |
+------------+
|        8.5 |
+------------+
1 row in set (0.00 sec)

（2） 总和（sum）

mysql> select sum(price) from product;
+-------------------+
| sum(price)        |
+-------------------+
| 69.99999809265137 |
+-------------------+
1 row in set (0.00 sec)

（3） 平均数（avg）

mysql> select avg(price) from product;
+--------------------+
| avg(price)         |
+--------------------+
| 13.999999618530273 |
+--------------------+
1 row in set (0.00 sec)

        总计及总计的和 

mysql> select *,price*qty as 总计 from product;
+----+-----------+-------+-----+-------------------+
| id | name      | price | qty | 总计              |
+----+-----------+-------+-----+-------------------+
|  1 | 香蕉      |   8.5 | 200 |              1700 |
|  2 | 苹果      |  12.5 | 400 |              5000 |
|  3 | 菠萝      |  12.4 |  70 | 867.9999732971191 |
|  4 | 哈密瓜    |  18.3 | 400 | 7319.999694824219 |
|  5 | 西瓜      |  18.3 | 400 | 7319.999694824219 |
+----+-----------+-------+-----+-------------------+
5 rows in set (0.00 sec)

mysql> select sum(总计) from (select *,price*qty as 总计 from product) as a;
+--------------------+
| sum(总计)          |
+--------------------+
| 22207.999362945557 |
+--------------------+
1 row in set (0.00 sec)