MySQL数据库简单操作

1.C:\Users\Administrator>mysql -u root -p//客户端连接数据库服务器
Enter password://有密码输入密码登录,没有直接回车进入

2.mysql> show databases;//显示数据库

3.mysql> drop database if exists class_13_db1//删除数据库

4.mysql> create database mydatabase;//创建数据库

5.mysql> use mydatabase;//使用数据库

6.mysql> create table student(
-> name varchar(20),
-> sex varchar(4),
-> id int(20),
-> chinses int(4),
-> math int(4),
-> english int(4)
-> );//创建表

7.mysql> show tables;显示当前数据库中有哪些表

8.mysql> insert into student (name,sex,id,chinses,math,english) values
-> (‘孙加成’,‘男’,0201,85,78,99),
-> (‘孙建明’,‘男’,0202,78,88,86),
-> (‘马浩东’,‘男’,0203,66,78,68),
-> (‘李佳琪’,‘女’,0204,99,78,92);//多行插入数据

9.mysql> insert into student values (‘孙浩’,‘男’,0205,65,45,52);//单行插入数据

10.mysql> select * from student;//全列查找

11.mysql> select name,id from student;//指定列查找

12.mysql> select name,chinses+math+english from student;//查询字段为表达式

13.mysql> select name,chinses+math+english 总分 from student;//起别名,这里总分就是表达式chinses+math+english的别名

14.mysql> select distinct math from student;//去重

15.mysql> select name,chinses,id from student order by id;//排序，默认为从小到大排序

16.mysql> select name,chinses,id from student order by id desc;//从大到小排序

17.mysql> select name,chinses+math+english total from student order by total desc;
±----------±------+
| name | total |
±----------±------+
| 李佳琪 | 269 |
| 孙加成 | 262 |
| 孙建明 | 252 |
| 马浩东 | 212 |
| 孙浩 | 162 |
±----------±------+
5 rows in set (0.00 sec)

18.mysql> select name from student where math<80;//条件查询，可以使用表达式但不能使用别名！

mysql> select name from student where (chinses>90 or math<80)&&english >80;

mysql> select name from student where math between 70 and 80;

19.mysql> select name from student where name like ‘孙%’;//模糊查询

mysql> select name from student where name like ‘孙_’;
±-------+
| name |
±-------+
| 孙浩 |
±-------+
1 row in set (0.00 sec)

20.mysql> update student set math=math+10 where name=‘孙建明’;//修改数据

21.mysql> delete from student where name=‘孙建明’;//删除数据