mysql基本语句

–mysql基本语句

–连接数据库
mysql -u root -p;

–创建数据库
mysql> create database school;

–展示数据库
mysql> show databases;

–删除数据库
mysql> drop database school;

–使用数据库
mysql> use school;

–创建数据表

create table student(
	sid int,
	sno varchar(10),
	sname varchar(20)
);

–展示数据表

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+

–删除数据表
mysql> drop table student;

–插入数据
mysql> insert into student values(2,“17022”,“jy”);
mysql> insert into student (sid,sno,sname) values(2,“17022”,“jy”);
–如果就在这个表中 应该就不需要加入字段名称.

–查询所有
mysql> select * from student;

+------+-------+-------+
| sid  | sno   | sname |
+------+-------+-------+
|    1 | 17021 | jy    |
|    2 | 17022 | jy    |
|    3 | 17023 | jyy   |
|    4 | 17025 | ly    |
+------+-------+-------+

–查询部分数据
select * from student where sname=“jy”;

mysql> select * from student where sname=“jy”;

+------+-------+-------+
| sid  | sno   | sname |
+------+-------+-------+
|    1 | 17021 | jy    |
|    2 | 17022 | jy    |
+------+-------+-------+

–更新数据
update student set sname=“wzx” where sno=“17022”;

+------+-------+-------+
| sid  | sno   | sname |
+------+-------+-------+
|    1 | 17021 | jy    |
|    2 | 17022 | wzx   |
|    3 | 17023 | jyy   |
|    4 | 17025 | ly    |
+------+-------+-------+

–删除数据
mysql> delete * from student where sid=3;

+------+-------+-------+
| sid  | sno   | sname |
+------+-------+-------+
|    1 | 17021 | jy    |
|    2 | 17022 | wzx   |
|    4 | 17025 | ly    |
+------+-------+-------+

–创建带有主键的数据表
–！！注意，key中id的单引号为 ~ 这里的引号

 create table tercher(
    id int,
    name varchar(20),
    sex varchar(10),
    age int(15),
    primary key(`id`)
     );

–添加主键(创建的时候忘记添加主键)
–首先确保主键不为空,然后添加主键
alter table student modify sid int not null;
alter table student add primary key(sid);

–删除主键
alter table student drop primary key;

–创建带有主键且主键自增的表 【auto_increment：自增】 且字段非空(not null)

create table user(  
    userid int ( 4 ) primary key not  null  auto_increment,  
    username varchar(16 ) not  null ,  
    userpassword varchar(32 ) not  null   
    );  

–union 连接查询 重复的值只显示一遍
select age from tercher union select age from parent order by age;

+------+
| age  |
+------+
|   19 |
|   20 |
|   22 |
+------+

–union all 用来显示所有
select age from tercher union all select age from parent order by age;

–order by 排序：默认为升序asc，也可以降序为desc
select * from tercher order by age;

+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  2 | ly   | 女   |   19 |
|  1 | jy   | 女   |   20 |
|  7 | wzx  | 男   |   22 |
+----+------+------+------+

select * from tercher order by age desc;

+----+------+------+------+
| id | name | sex  | age  |
+----+------+------+------+
|  7 | wzx  | 男   |   22 |
|  1 | jy   | 女   |   20 |
|  2 | ly   | 女   |   19 |
+----+------+------+------+

–group by 进行分组 并用函数count() 统计总数
select sname ,count() from student group by sname;

+-------+----------+
| sname | count(*) |
+-------+----------+
| jy    |        2 |
| wzx   |        1 |
| ly    |        1 |
+-------+----------+

–with rollup 实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）
mysql> SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name with rollup;

+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL |           16 |
+--------+--------------+

–连接使用 inner join [tercher inner join student]可省略 left join 可显示左边的数据 right join 右边
select a.id,a.name,a.age,b.sname from tercher a ,student b where a.name=b.sname;

+----+------+------+-------+
| id | name | age  | sname |
+----+------+------+-------+
|  1 | jy   |   20 | jy    |
|  7 | wzx  |   22 | wzx   |
|  1 | jy   |   20 | jy    |
|  2 | ly   |   19 | ly    |
+----+------+------+-------+