https://www.cnblogs.com/liangmingda/p/13548466.html
基本的SQL語句有select、insert、update、delete、create、drop、grant、revoke等,具體使用方式見表: 1 庫操作 - 創(chuàng)建數(shù)據(jù)庫:create database shujuku
- 創(chuàng)建帶字符集的數(shù)據(jù)庫:create database mydb2 CHARACTER SET=UTF8
- 創(chuàng)建帶校驗的數(shù)據(jù)庫:create database mydb3 CHARACTER SET=UTF8 COLLATE utf8_general_ci
- 顯示數(shù)據(jù)庫:show databases
- 刪除數(shù)據(jù)庫:drop database shujuku
- 修改數(shù)據(jù)庫編碼:alter databaese shujuku character set gb2312
2 表操作 創(chuàng)建數(shù)據(jù)庫表(創(chuàng)建一個表名為:employee,該表中含有id、name、sex、birthday、job字段); create table employee ( id int, name varchar(40), sex char(4), birthday date, job varchar(40), ); - 表中增加image字段:alter table employee add image blob
- 修改job值,使其長度為60(原長度為1000):alter table employee modify job varchar(60)
- 刪除sex列:alter table employee drop sex;
- 表名改為user(原名為employee):rename table employee to user
- 修改表的字符集為utf_8:alter table user character set utf8
- 列name修改為 username:alter table change columm name username varchar(100)
- 刪除表:drop table user
3 增刪改查實例 準備表: create table employee ( id int, name varchar(40), sex varchar(4), birthday date, entry_date date, salary decimal(8,2), resume text ); 插入表數(shù)據(jù): insert into employee(id ,name,sex,birthday,entry_date,salary,resume) values(1,'zhangsan','male','1999-08-22','2020-08-22,'1000','i am a developer'); - 指定某些列插入數(shù)據(jù):insert into employee(id) values(6)
- 插入漢字:insert into employee(id,name) values(6,'張三’)
4 修改表數(shù)據(jù) - 將所有員工薪水修改為5000元:update employee set salary=5000
- 將姓名為’zs’的員工薪水修改為3000元:update employee set salary = 3000 where name='zhangsan’
- 將姓名為’aaa’的員工薪水修改為4000元,job改為ccc:update employee set salary = 4000,job='ccc’ where name='張三’
- 將wu的薪水在原有基礎上增加1000元:update employee set salary = salary 1000 where name='張三’
5 刪除表數(shù)據(jù) - 刪除表中名稱為“zs”的記錄:delete from employee where job='ccc’
- 刪除表中所有記錄:delete from employee
- 使用truncate刪除表中記錄:truncate table employee
6 查詢表數(shù)據(jù) - 查詢表中所有學生的信息:select id,name,chinese,english,math from student
- 查詢表中所有學生的姓名和對應的英語成績:select name,english from student
- 查詢姓名為wu的學生成績:select * from student where name='張三’
- 查詢英語成績大于90分的同學:select * from student where english>'90’
- 查詢英語分數(shù)在 80-90之間的同學:select * from student where english>=80 and english=<90
7 常見MySQL語句命令 - 進入mysql 命令行:mysql -uroot -p
- 查看所有數(shù)據(jù)庫:show databases
- 創(chuàng)建數(shù)據(jù)庫:create database niu charset utf8
- 刪除數(shù)據(jù)庫:drop database niu
- 選擇數(shù)據(jù)庫:use databases
- 查看所有表:show tables
- 查看創(chuàng)建數(shù)據(jù)庫的語句:show create database databasename
- 查看創(chuàng)建表的語句:show create table tablename
- 查看表結構:desc tablenmae
8 常見MySQL字段含義 - 自增長:auto_increment
- 非空:not null
- 默認值:default
- 唯一:unique
- 指定字符集:charset
- 主鍵:primary key
|