MySQL exercises one

DROP TABLE IF EXISTS ' Liuyan '; CREATE TABLE ' Liuyan ' (' id ' int (11) not NULL auto_increment, ' title ' varchar (32) not NULL, ' author ' varchar (16default NULL, ' Addtime ' datetime DEFAULT null, ' content ' text, ' status ' char (1) Not NULL DEFAULT'0', PRIMARY KEY (' id ')) ENGINE=innodb auto_increment=6 DEFAULT charset=UTF8;-- ------------------------------Records of Liuyan-- ----------------------------INSERT into ' Liuyan ' VALUES ('1','Introduction','Nobita','2017-02-14 09:59:37','brother is not a good horse, but also not a common donkey','0'INSERT into ' Liuyan ' VALUES ('2','Jingle Cat','Bear Bears','2016-02-16 09:59:44','you have leek in your teeth .','0'INSERT into ' Liuyan ' VALUES ('3','Floral','Miao Miao','2017-05-28 09:59:52','Miao Miao asked Flower: Sell Meng is commendatory term or derogatory word? ','0'INSERT into ' Liuyan ' VALUES ('4','Brother Xia','Nobita','2017-08-29 09:59:57','Fighting Color Buddha','0'INSERT into ' Liuyan ' VALUES ('5','Morning Morning','Tease ratio','2010-06-22 10:00:03','you smile like a chrysanthemum, chrysanthemum remnant, man butt injury','0');

Build Table

1. Create message database: LIUYANDB;

2. Create a message form Liuyan in the LIUYANDB database with the following structure:

Table name		Liuyan		Message Information Form
Serial number	Field name	Field description	Type	Property	Note
1	Id	Number	Int	Non-empty	Primary key, self-increment 1
2	Title	Title	varchar (32)	Non-empty
3	Author	Author	varchar (16)	Can be empty
4	Addtime	Message time	Datetime	Non-empty
5	Content	Message content	Text	Non-empty
6	Isdelete	Whether to delete	char (1)	Non-empty	Default value 0

3. Add a column status at the end of the message list (status char (1) default value 0)

4. Modify the message list author default value is ' Youku ', set to non-empty

5. Delete the Isdelete field in the Liuyan table

6. Add >5 test data to the message table

　　

7. Request to change the value of author field to admin in information with ID value greater than 3

8. Delete the data with ID number 4.

Additional questions:

1. Add >15 test data to the message table, require three users to add
2. Check all message information
3. Query a user's message information.
4. Queries all data, sorted by time in descending order.
5. Get message with ID from 2 to 6 and sort by time descending
6. Count the number of messages left by each user and sort them from small to large.
7. Change the author of two data with ID 8 and 9 to ' Doudou '.
8. Take out the latest three messages.
9. Query message contains "a" letter message, and according to the message time from small to large sort
10. Delete "Author" duplicate data and retain one author with the largest ID

MySQL exercises one