mysql基础语法

mysql语法

登陆数据库

mysql -u root -p (回车)
(密码)

SQL语句种类

1. DDL(数据定义语言)

查看所有数据库

mysql> show databases;

运行结果
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

创建数据库

create table 库名;
mysql> create database aa; //创建叫做aa的数据库

mysql> show databases; //再次查看所有的数据库,多了个aa的库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aa                 |
| mysql              |
| performance_schema |
| test               |
+--------------------+

删除数据库

dorp database 要删除的库名;

mysql> drop database aa; //删除aa的库
mysql> show databases;  //aa已经被删除
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+


使用数据库

use 库名

mysql> use aa;  
Database changed

创建表,查看表,删除表

 create table 表名(字段1 类型,字段2 类型......);

 mysql> create table bb(id int,xm varchar(20));//创建bb的表,里面创建2个字段
 mysql> show tables;  //查看当前库里所有的表
 +--------------+
| Tables_in_aa |
+--------------+
| bb           |
+--------------+

mysql> desc bb;//查看里面的内容
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| xm    | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> drop table bb;//删除表

只删除字段

alert table 表名 drop 要删除的字段;

mysql> alter table bb drop xm;
mysql> desc bb;//结果
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

添加表里面的字段

alter table 表名 add 字段 字段类型;

mysql> alter table bb add xh varchar(20);
mysql> desc bb;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| xh    | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

修改字段名和属性

alter table 表名 change 字段 要修改的字段 类型; 

mysql> alter table bb change xh xm int;
mysql> desc bb;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| xm    | int(11) | YES  |     | NULL    |       |

2. DML(数据操作语言)

插入语句INSERT INTO

insert into 插入的表(字段1,字段2,...values (1,值2,...);

mysql> insert into aa (username,password) values ('admin','root');

查询语句

select * from 表名;  //*通配符

mysql> select * from aa;
+----------+----------+
| username | password |
+----------+----------+
| admin    | root     |
+----------+----------+
··················································································
也可以
select 要查询的字段 from 表名; 

mysql> select username from aa;
+----------+
| username |
+----------+
| admin    |
+----------+

多插入几个值
mysql> select * from aa;
+-----------+----------+
| username  | password |
+-----------+----------+
| admin1111 | root     |
| admin     | 123456   |
| admin     | aaaaaa   |
| admin     | bbbbbb   |
+-----------+----------+
增加条件查询
mysql> select * from aa where username='admin';查询username='admin'的值
+----------+----------+
| username | password |
+----------+----------+
| admin    | 123456   |
| admin    | aaaaaa   |
| admin    | bbbbbb   |
+----------+----------+

更新数据

update 表名 set 要修改的字段=要更新的数据

mysql> update aa set username='admin';//将username里面的数据全部更新为admin
mysql> select * from aa;
+----------+----------+
| username | password |
+----------+----------+
| admin    | root     |
| admin    | 123456   |
| admin    | aaaaaa   |
| admin    | bbbbbb   |
+----------+----------+

如果只更新一条,增加条件即可
mysql> update aa set 
username='admin1111' where password='root';//更新username字段,条件password等于root
mysql> select * from aa;
+-----------+----------+
| username  | password |
+-----------+----------+
| admin1111 | root     |
| admin     | 123456   |
| admin     | aaaaaa   |
| admin     | bbbbbb   |
+-----------+----------+


删除数据

delete from 表名 where 字段=‘值’

mysql> delete from aa where username='admin1111'; //删除username字段为admin1111的数据
mysql> select * from aa;
+----------+----------+
| username | password |
+----------+----------+
| admin    | 123456   |
| admin    | aaaaaa   |
| admin    | bbbbbb   |
+----------+----------+

3. DCL(数据控制语言)

排序

select*fromorder by 字段 ASC(/DESC) //ASC(默认)升序,DESC降序

mysql> select * from aa; 
+----------+------------+
| username | password   |
+----------+------------+
| admin1   | 1234567    |
| admin    | 123456     |
| admin    | aaaaaa     | //默认排序
| admin    | bbbbbb     |
| admin2   | 12345678   |
| admin3   | 123456789  |
| admin4   | 1234567890 |
+----------+------------+

mysql>select * from aa order by username DESC;//倒序
+----------+------------+
| username | password   |
+----------+------------+
| admin4   | 1234567890 |
| admin3   | 123456789  |
| admin2   | 12345678   |
| admin1   | 1234567    |
| admin    | 123456     |
| admin    | aaaaaa     |
| admin    | bbbbbb     |
+----------+------------+

limit分页

select * from aa limit n,m; //n表示从第几行开始,m表示取几条


mysql> select * from aa;
+----------+------------+
| username | password   |
+----------+------------+
| admin1   | 1234567    |
| admin    | 123456     |
| admin    | aaaaaa     |
| admin    | bbbbbb     |
| admin2   | 12345678   |
| admin3   | 123456789  |
| admin4   | 1234567890 |
+----------+------------+

mysql> select * from aa limit 0,2;//从第一行开始取2条数据
+----------+----------+
| username | password |
+----------+----------+
| admin1   | 1234567  |
| admin    | 123456   |
+----------+----------+
mysql> select * from aa limit 0,1;/从第一行开始取1条数据
+----------+----------+
| username | password |
+----------+----------+
| admin1   | 1234567  |
+----------+----------+
mysql> select * from aa limit 3,2;//从第四行开始取2条数据
+----------+----------+
| username | password |
+----------+----------+
| admin    | bbbbbb   |
| admin2   | 12345678 |
+----------+----------+
2 rows in set (0.00 sec)

模糊查询

select * fromwhere 字段 like; //like跟等于差不多,like像的意思

mysql> select * from aa where username like 'admin';//把aa表里面像admin的都取出来
+----------+----------+
| username | password |
+----------+----------+
| admin    | 123456   |
| admin    | aaaaaa   |
| admin    | bbbbbb   |
+----------+----------+


mysql> select * from aa where username like '%a%'; 
// %a%数据里面有a的都取出来,如果是a%把开头是a的取出来,%a把结尾是a的取出来
+----------+------------+
| username | password   |
+----------+------------+
| admin1   | 1234567    |
| admin    | 123456     |
| admin    | aaaaaa     |
| admin    | bbbbbb     |
| admin2   | 12345678   |
| admin3   | 123456789  |
| admin4   | 1234567890 |
+----------+------------+

mysql> select * from aa where password like '%8';//结尾是8的取出来
+----------+----------+
| username | password |
+----------+----------+
| admin2   | 12345678 |
+----------+----------+

运算符

运算符号 + - * / %
逻辑运算符 and(&&) not(!!) or(||)

算术运算

mysql> select 9+1;//加
+-----+
| 9+1 |
+-----+
|  10 |
+-----+

mysql> select 9-1;//减
+-----+
| 9-1 |
+-----+
|   8 |
+-----+

mysql> select 9*2; //乘
+-----+
| 9*2 |
+-----+
|  18 |
+-----+

mysql> select 9/3;//除
+--------+
| 9/3    |
+--------+
| 3.0000 |
+--------+

mysql> select 9%3;//取于
+------+
| 9%3  |
+------+
|    0 |
+------+

逻辑运算

mysql> select * from aa;
+----------+------------+
| username | password   |
+----------+------------+
| admin1   | 1234567    |
| admin    | 123456     |
| admin    | aaaaaa     |
| admin    | bbbbbb     |
| admin2   | 12345678   |
| admin3   | 123456789  |
| admin4   | 1234567890 |
+----------+------------+

mysql> select * from aa where username = 'admin' and password=123456;
//查询username字段等于admin,并且password等于123456的数据
+----------+----------+
| username | password |
+----------+----------+
| admin    | 123456   |
+----------+----------+

mysql> select * from aa where username = 'admin' or password='aaaaaa';
//查询usernam等于admin或password等于aaaaaa的数据
+----------+----------+
| username | password |
+----------+----------+
| admin    | 123456   |
| admin    | aaaaaa   |
| admin    | bbbbbb   |
+----------+----------+

mysql> select * from aa where not username = 'admin';
//查询username里不等于admin的数据
+----------+------------+
| username | password   |
+----------+------------+
| admin1   | 1234567    |
| admin2   | 12345678   |
| admin3   | 123456789  |
| admin4   | 1234567890 |
+----------+------------+

联合查询

union



mysql> select * from aa where username='admin' union select * from aa where username='admin4';
//将2个查询到的合在一起输出,可以是不同的表,但是字段必须相同
+----------+------------+
| username | password   |
+----------+------------+
| admin    | 123456     |
| admin    | aaaaaa     |
| admin    | bbbbbb     |
| admin4   | 1234567890 |
+----------+------------+

子查询

select * from user where username = (select username from admin where id=1);
//admin表当id=1的用户名是否在user表也存在

mysql> select * from aa where username=(select 'admin');
//相当于select * from aa where username='admin'
+----------+----------+
| username | password |
+----------+----------+
| admin    | 123456   |
| admin    | aaaaaa   |
| admin    | bbbbbb   |
+----------+----------+
©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页