《MySQL基础教程》笔记4

D:\phpstudy_pro\Extensions\MySQL5.7.26\bin

# 创建
create table salary(empid varchar(10), sales int , month int);
# 列结构
mysql> desc salary;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empid | varchar(10) | YES  |     | NULL    |       |
| sales | int(11)     | YES  |     | NULL    |       |
| month | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

select * from salary

mysql> select * from salary
+-------+-------+-------+
| empid | sales | month |
+-------+-------+-------+
| A103  |   101 |     4 |
| A102  |    54 |     5 |
| A104  |   181 |     4 |
| A101  |   184 |     4 |
| A103  |    17 |     5 |
+-------+-------+-------+
5 rows in set (0.00 sec)

select sales,empid from salary;

mysql> select sales,empid from salary;
+-------+-------+
| sales | empid |
+-------+-------+
|   101 | A103  |
|    54 | A102  |
|   181 | A104  |
|   184 | A101  |
|    17 | A103  |
|   300 | A101  |
|   205 | A102  |
|    93 | A104  |
|    12 | A103  |
|    87 | A107  |
+-------+-------+
10 rows in set (0.00 sec)

mysql> select sales,empid,sales from salary;
+-------+-------+-------+
| sales | empid | sales |
+-------+-------+-------+
|   101 | A103  |   101 |
|    54 | A102  |    54 |
|   181 | A104  |   181 |
|   184 | A101  |   184 |
|    17 | A103  |    17 |
|   300 | A101  |   300 |
|   205 | A102  |   205 |
|    93 | A104  |    93 |
|    12 | A103  |    12 |
|    87 | A107  |    87 |
+-------+-------+-------+
10 rows in set (0.00 sec)

select 列名 as 别名 from 表名;

mysql> select sales as 销售 from salary;
+--------+
| 销售   |
+--------+
|    101 |
|     54 |
|    181 |
|    184 |
|     17 |
|    300 |
|    205 |
|     93 |
|     12 |
|     87 |
+--------+
10 rows in set (0.00 sec)

运算符         使用示例     含义
+				a+b		 a加上b
-				a-b		 a减去b
*				a*b		 a乘以b
/				a/b		 a除以b
div				a div b	  a除以b，结果取整
%、mod			a % b     a除以b，取余数

mysql> select sales*10000 as 销售 from salary;
+---------+
| 销售    |
+---------+
| 1010000 |
|  540000 |
| 1810000 |
| 1840000 |
|  170000 |
| 3000000 |
| 2050000 |
|  930000 |
|  120000 |
|  870000 |
+---------+
10 rows in set (0.00 sec)

 select 函数名(支持计算的列名) from 表名;

mysql> select avg(sales) from salary;
+------------+
| avg(sales) |
+------------+
|   123.4000 |
+------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| company    |
+------------+
1 row in set (0.00 sec)

mysql> select charset('utf-8');
+------------------+
| charset('utf-8') |
+------------------+
| utf8             |
+------------------+
1 row in set (0.00 sec)

select concat(字符串或列名1,字符串或列名2..) from 表名;

mysql> select concat(empid,'销售',sales,'万') from salary;
+------------------------------------+
| concat(empid,'销售',sales,'万')    |
+------------------------------------+
| A103销售101万                      |
| A102销售54万                       |
| A104销售181万                      |
| A101销售184万                      |
| A103销售17万                       |
| A101销售300万                      |
| A102销售205万                      |
| A104销售93万                       |
| A103销售12万                       |
| A107销售87万                       |
+------------------------------------+
10 rows in set (0.00 sec)

select right(列名,字符个数N) from 表名;

mysql> select right(empid,2) from salary;
+----------------+
| right(empid,2) |
+----------------+
| 03             |
| 02             |
| 04             |
| 01             |
| 03             |
| 01             |
| 02             |
| 04             |
| 03             |
| 07             |
+----------------+
10 rows in set (0.00 sec)

select left(列名,字符个数N) from 表名;

mysql> select left(empid,2) from salary;
+---------------+
| left(empid,2) |
+---------------+
| A1            |
| A1            |
| A1            |
| A1            |
| A1            |
| A1            |
| A1            |
| A1            |
| A1            |
| A1            |
+---------------+
10 rows in set (0.00 sec)

select repeat(替换的字符,数据类型的列名) from 表名;

mysql> select repeat('*',month) from salary;
+-------------------+
| repeat('*',month) |
+-------------------+
| ****              |
| *****             |
| ****              |
| ****              |
| *****             |
| *****             |
| ******            |
| *****             |
| ******            |
| ******            |
+-------------------+
10 rows in set (0.00 sec)

select reverse(列名) from 表名;

mysql> select reverse(empid) from salary;
+----------------+
| reverse(empid) |
+----------------+
| 301A           |
| 201A           |
| 401A           |
| 101A           |
| 301A           |
| 101A           |
| 201A           |
| 401A           |
| 301A           |
| 701A           |
+----------------+
10 rows in set (0.00 sec)

 create table work (id int auto_increment primary key, time datetime );

# 显示列表结构
mysql> desc work;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| time  | datetime | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
# 插入数据
mysql> insert into work (time) values(now());
Query OK, 1 row affected (0.00 sec)
# 查询
mysql> select * from work;
+----+---------------------+
| id | time                |
+----+---------------------+
|  1 | 2023-11-27 11:57:11 |
+----+---------------------+
1 row in set (0.00 sec)

select 列名 from 表名 limit 显示个数;

mysql> select name from info limit 4;
+------------+
| name       |
+------------+
| 笔友城堡     |
| 笔友城堡3    |
| dushu      |
| shu        |
+------------+
4 rows in set (0.00 sec)